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 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 options
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 |
The From Datasource can be one of the following:
| table_spec | 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. |
| JOIN Clause | Joins one or more tables. |
The WHERE clause can be used to specify both criteria and table join information.
select * from trans_table where state = 'CA';
The ORDER BY clause returns selected rows in ascending or descending order by the specified column_spec or the ordinal position of a column_spec 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 column_spec 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_spec can also be prefixed with 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 = 'inform*';