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 | ALL >]
[ SAMPLE(‘control_break’ [, ’qualifier’]) ]
[ UNION [ALL] | INTERSECT | EXCEPT ]
[ * ] | [ TABLE.]COLUMN [column_alias] [, TABLE.column ... ]
[ (subquery) ]
[ aggregate_function( [TABLE.]COLUMN ) ]
[ $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 | column_position ] [, column | column_position ]
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 |
One of the following:
| table | A table using a syntax of[database.]table. |
| $OMNIDEX | A dummy table consisting of one row and one column. |
| (subquery) | A nested subquery. |
Order by column is in the format of [database.][table.]column or table_alias.column.
Column can also be an alias name of the column.
> select * from mytable
> select company, contact from mytable where company = 'Information';