Differences

This shows you the differences between two versions of the page.

--- dev:sql:statements:select:home [2010/07/02 03:07]
tdo
+++ dev:sql:statements:select:home [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 {{page>:top_add&nofooter&noeditbtn}}
-<html><div align="center"><span style="color:red">DRAFT</span></div></html>
-====== Omnidex SQL Statement: SELECT ======
 {{page>:sql_bar&nofooter&noeditbtn}}
+====== Omnidex SQL Statement: SELECT ======
+^See Also:^[[dev:sql:statements:select:from |FROM clause]]^[[dev:sql:statements:select:where | WHERE clause ]]^[[dev:sql:statements:select:group_by | GROUP BY clause]]^[[dev:sql:statements:select:order_by | ORDER BY clause ]]^
 ===== Description =====
 The Omnidex SQL Select statement is the primary statement used to query records on Omnidex enhanced data.
@@ Line 8: / Line 9: @@
 Omnidex will automatically optimize the SELECT statement using Omnidex indexes.
 ===== Syntax =====
-The concise syntax for the Omnidex SELECT statement is the following:
+The syntax for the Omnidex SELECT statement is the following:
-<code SQL>
+<code>
-  SELECT expression_list
+  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 clause ]
+      [ WHERE criteria_specification ]
-      [ GROUP BY expression_list ]
+      [ GROUP BY group_by_specification ]
-          [ HAVING predicates ]
+          [ HAVING having_specification ]
-      [ ORDER BY expression_list ]
+      [ ORDER BY order_by_specifcation ]
+      [ < UNION [ALL] | INTERSECT | EXCEPT > select_statement ]
       [ ON [ CURSOR ] cursor_no ] | [ INSTANCE instance_no ]
-      [ WITH options ]
+      [ WITH option_list ]
 </code>
-==== Expression_list syntax: ====
+==== Select_list syntax: ====
-<code SQL>
+<code>
-  expression_list
+         [ * ] | [ column_spec [column_alias] [, column_spec ... ]
-         [ < 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 ) ]
+         [ aggregate_function( column_spec ) [column_alias] ]
+         [ function( column_spec ) ]
+         [ column_spec < + | - | / | * > <column_spec | value > [column_alias] ]
          [ $uniquekey | $odxid ]
 </code>
-==== FROM Clause syntax: ====
+==== FROM datasource: ====
+^See:^[[dev:sql:statements:select:from | FROM datasource Discussion ]]^
 <code>
-  FROM [database.]table1 [ table_alias ] [, [database.]table2 [ table_alias ] [ $omnidex ] ]
+  FROM table_spec [ table_alias ] [, [table_spec2 [ table_alias ] [ $omnidex ] ]
-       [[ < LEFT | RIGHT >][ OUTER ] ]
+           [[ < LEFT | RIGHT >][ OUTER | CROSS ] ]
-           [ JOIN [database.]table2 ]
+       [ JOIN table_spec2 ]
-           [ ON table1.linkfield = table2.linkfield ]
+           [ ON table_spec1.linkfield = table_spec2.linkfield ]
-           [ <AND| OR> NOT] [database.]tableN.linkfield = [database.]tableN.linkfield ]
+           [ <AND | OR> NOT] table_spec.linkfield = table_spec.linkfield ]
 </code>
-==== WHERE Clause Syntax: ====
+==== WHERE criteria_specification Syntax: ====
+^See:^[[dev:sql:statements:select:where | WHERE criteria_specification Discussion ]]^
 <code>
   WHERE [ criteria_list ]
@@ Line 49: / Line 53: @@
         [ IN (subquery) ]
 </code>
+==== GROUP BY/HAVING group_by_specification Syntax: ====
-==== GROUP BY Clause Syntax: ====
+^See: ^[[dev:sql:statements:select:group_by | GROUP BY group_by_specification Discussion]]^
 <code>
   GROUP BY group_by_columns
-      HAVING [ left_operand operator subquery ]
+      [ HAVING [ left_operand operator subquery ]
-             [ left_operand IN (subquery) ]
+               [ left_operand IN (subquery) ] ]
 </code>
-==== ORDER BY Clause Syntax: ====
+==== ORDER BY order_by_specification Syntax:  ====
+^See:^[[dev:sql:statements:select:order_by | ORDER By order_by_specfication Discussion ]]^
 <code>
-  ORDER BY [column | column_position ] [, column | column_position ]
+  ORDER BY [  column_spec | column_position ] [ ASC | DESC ]
+           [, column_spec | column_position ] [ ASC | DESC ]
 </code>
-==== WITH Clause Syntax: ====
+==== WITH option_list: ====
 <code>
-  WITH options
+  WITH option_list
 </code>
@@ Line 70: / Line 76: @@
 The expression_list can be one of the following:
-|  *   | All columns for the named tables using a syntax of [/[  database. ] table. ]] * |
+|  *   | 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]] |
+| 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 | |
-==== table_list table_spec [join_syntax ] ====
-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. |
-==== FROM Clause ====
-==== WHERE Clause ====
 ==== GROUP BY Clause ====
 ==== HAVING Clause ====
-==== ORDER BY Clause ====
-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.
 ==== WITH options ====
-=== PRESERVE_CRITERIA ===
+== PRESERVE_CRITERIA ==
-=== PRESERVE_CRITERIA_ORDER ===
+== PRESERVE_CRITERIA_ORDER ==
 ===== Examples =====
 ==== Select all columns from table ====
-  > select * from mytable
+  > select * from mytable;
 ==== Select with Omnidex criteria on Quicktext column ====
-  > select company, contact from mytable where company = 'Information';
+  > 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;
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

dev/sql/statements/select/home.1278040048.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes