This is an old revision of the document!
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 concise syntax for the Omnidex SELECT statement is the following:
SELECT expression_list
FROM data_source
[ WHERE clause ]
[ GROUP BY expression_list ]
[ HAVING predicates ]
[ ORDER BY expression_list ]
[ ON [ CURSOR ] cursor_no ] | [ INSTANCE instance_no ]
[ WITH options ]
expression_list
[ < TOP (n)
[ SKIP(n) | EVERY(n) | RANDOM (n [, seed] ) ] ]
[ < DISTINCT column_spec | ALL >]
[ SAMPLE(‘control_break’ [, ’qualifier’]) ]
[ UNION [ALL] | INTERSECT | EXCEPT ]
[ * ] | [ 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 [database.]table1 [ table_alias ] [, [database.]table2 [ table_alias ] [ $omnidex ] ]
[[ < LEFT | RIGHT >][ OUTER ] ]
[ JOIN [database.]table2 ]
[ ON table1.linkfield = table2.linkfield ]
[ <AND | OR> NOT] [database.]tableN.linkfield = [database.]tableN.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 options
The expression_list can be one of the following:
| * | All columns for the named tables using a syntax of [/[ database. ] table. ]] * |
| column | A column, optionally qualified by a table, optionally with an alias using a syntax of [/[database.]table.]column [/[AS] column_alias]] |
| (subquery) | A nested subquery that returns a single select item. |
| expression | A standard SQL or Omnidex-specific function |
table_list table_spec [join_syntax ]
Table_spec can be one of the following:
| table | A table using a syntax of [database.]table. |
| $OMNIDEX | An internal table automatically created and used for testing consisting of one row and one column. |
| (subquery) | A nested subquery. |
The ORDER BY clause returns selected rows in ascending or descending order by the specified column or the ordinal position of a column in the column list.
select trans_date, trans_amount from trans_table order by trans_date;
select trans_date, trans_amount from trans_table order by 2;
The Order by column can be in the format of [database.][table.]column or table_alias.column.
select trans_date, trans_amount from trans_table order by db1.trans_table.trans_amount;
Column can also be an alias name of the table's column or an alias name of an expression.
select trans_date, trans_amount - discount NetAmount from trans_table order by NetAmount;
The ASC/DESC modifier can be used to return the data in ascending or descending order with ascending order being the default.
select trans_date, trans_amount from trans_table order by trans_date desc;
> select * from mytable
> select company, contact from mytable where company = 'Information';