Omnidex SQL SELECT

The Omnidex SQL Select statement is the primary statement used to query records on Omnidex enhanced data.

  SELECT expression_list
      FROM data_source
      [ WHERE predicates ]
      [ 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 table1 [ table_alias ] [, table2 [ table_alias ] [ $omnidex ] ]
       [ LFET | RIGHT ]
           [ OUTER ]
           [ JOIN ]
           [ , table2 ]
           [ ON table1.linkfield = table2.linkfield ]

  WHERE 
       [ EXISTS (subquery) ]
       [ IN (subquery) ]

  GROUP BY group_by_columns
      HAVING [ left_operand operator subquery ]
             [ left_operaand IN (subquery) ]

  ORDER BY [COLUMN | POSITION ] [, COLUMN | POSITION ]

One of the following:

• All columns for the named tables using a syntax of:

[ [ [ environment. ] database. ] table. ] *

       column      A column, optionally qualified by a table, optionally with an alias
                   using a syntax of [[[environment.]database.]table.]column [[AS] column_alias]

       (subquery)  A nested subquery that returns a single select item

       expression  An standard SQL or Omnidex-specific function

table_list

    table_spec [join_syntax
    
    One of the following:

        table      A table using a syntax of:
                   [[[environment.]database.]table

        $OMNIDEX   A dummy table consisting of one row and one column.
        
        (subquery) A nested subquery 
          
       
join_syntax
     [<LEFT | RIGHT> [OUTER]] JOIN [[[environment.]database.]table 
        ON join_predicate [<AND | OR> NOT] join_predicate]

join_predicate
     [[[environment.]database.]table.column = [[[environment.]database.]table.column
                   

> select * from mytable

> select company, contact from mytable