This is an old revision of the document!
See Also: | FROM clause | WHERE clause | GROUP BY clause | ORDER BY clause |
---|
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_list ] [ GROUP BY group_by_list ] [ HAVING having_list ] [ ORDER BY order_by_list ] [ < 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 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';
> 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;