Omnidex SQL Statement: SELECT

Description

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.

Syntax

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_specification ]
      [ GROUP BY group_by_specification ]
          [ HAVING having_specification ]
      [ ORDER BY order_by_specifcation ]
      [ < UNION [ALL] | INTERSECT | EXCEPT > select_statement ]
      [ ON [ CURSOR ] cursor_no ] | [ INSTANCE instance_no ]
      [ WITH option_list ]

Select_list syntax:

         [ * ] | [ 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 datasource:

  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_specification Syntax:

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

GROUP BY/HAVING group_by_specification Syntax:

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

ORDER BY order_by_specification Syntax:

  ORDER BY [  column_spec | column_position ] [ ASC | DESC ] 
           [, column_spec | column_position ] [ ASC | DESC ]

WITH option_list:

  WITH option_list

Discussion

Expression_list

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

GROUP BY Clause

HAVING Clause

WITH options

PRESERVE_CRITERIA
PRESERVE_CRITERIA_ORDER

Examples

Select all columns from table

> select * from mytable;

Select with Omnidex criteria on Quicktext column

> select company, contact from mytable where company = 'inform*';

Aggregates, Functions, Expressions

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 List Item Ordinal Numbers

SELECT company, contact, state FROM customers ORDER BY 3;
SELECT company, contact, state FROM customers ORDER BY 3, 1;
 
Back to top
dev/sql/statements/select/home.txt · Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0