The Omnidex SQL Select statement is the primary statement used to query records on Omnidex enhanced data.
Omnidex will automatically optimize the SELECT statement using Omnidex indexes.
The syntax for the Omnidex SELECT statement is the following:
SELECT
[ < TOP n [ SKIP n ] | EVERY n [ SKIP n ] | RANDOM n | RANDOM (n, seed) > ]
[ SAMPLE(‘control_break’ [, ’qualifier’]) ]
[ < DISTINCT column_spec | ALL >]
select_list
FROM data_source
[ WHERE criteria_specification ]
[ GROUP BY group_by_specification ]
[ HAVING having_specification ]
[ ORDER BY order_by_specifcation ]
[ < UNION [ALL] | INTERSECT | EXCEPT > select_statement ]
[ ON [ CURSOR ] cursor_no ] | [ INSTANCE instance_no ]
[ WITH option_list ]
[ * ] | [ column_spec [column_alias] [, column_spec ... ]
[ (subquery) ]
[ aggregate_function( column_spec ) [column_alias] ]
[ function( column_spec ) ]
[ column_spec < + | - | / | * > <column_spec | value > [column_alias] ]
[ $uniquekey | $odxid ]
FROM table_spec [ table_alias ] [, [table_spec2 [ table_alias ] [ $omnidex ] ]
[[ < LEFT | RIGHT >][ OUTER | CROSS ] ]
[ JOIN table_spec2 ]
[ ON table_spec1.linkfield = table_spec2.linkfield ]
[ <AND | OR> NOT] table_spec.linkfield = table_spec.linkfield ]
WHERE [ criteria_list ]
[ EXISTS (subquery) ]
[ IN (subquery) ]
GROUP BY group_by_columns
[ HAVING [ left_operand operator subquery ]
[ left_operand IN (subquery) ] ]
ORDER BY [ column_spec | column_position ] [ ASC | DESC ]
[, column_spec | column_position ] [ ASC | DESC ]
WITH option_list
The expression_list can be one of the following:
| * | All columns for the named tables using a syntax of [[database.]table.]] * |
| column_spec | A column optionally qualified by a database and table with an optional column_alias. |
| (subquery) | A nested subquery that returns a single select item. |
| expression | A standard SQL or Omnidex-specific function |
| $uniquekey | |
| $odxid |
> select * from mytable;
> select company, contact from mytable where company = 'inform*';
SELECT customer_no, sum(amount) FROM orders GROUP BY customer_no ORDER BY 2;
SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no ORDER BY 2, 1;
SELECT company, contact, state FROM customers ORDER BY 3;
SELECT company, contact, state FROM customers ORDER BY 3, 1;