Differences

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

--- dev:sql:statements:select:home [2010/07/02 04:29]
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>
-  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>
-  expression_list
-         [ < TOP (n)
-             [ SKIP(n) | EVERY(n) | RANDOM (n [, seed] ) ] ]
-         [ < DISTINCT column_spec | ALL >]
-         [ SAMPLE(‘control_break’ [, ’qualifier’]) ]
-         [ UNION [ALL] | INTERSECT | EXCEPT ]
          [ * ] | [ column_spec [column_alias] [, column_spec ... ]
          [ (subquery) ]
@@ Line 34: / Line 35: @@
          [ column_spec < + | - | / | * > <column_spec | value > [column_alias] ]
          [ $uniquekey | $odxid ]
 </code>
+==== FROM datasource: ====
-==== FROM Clause Syntax: ====
+^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 52: / Line 53: @@
         [ IN (subquery) ]
 </code>
-==== GROUP BY/HAVING Clause Syntax: ====
+==== GROUP BY/HAVING group_by_specification 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 ]
-             [ 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_spec | column_position ] [ ASC | DESC ]
+  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 73: / 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_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. |
@@ Line 79: / Line 82: @@
 | $uniquekey | |
 | $odxid | |
-==== FROM Clause ====
-Table_spec can be one of the following:
-| table | 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. |
-==== WHERE Clause ====
 ==== GROUP BY Clause ====
 ==== HAVING Clause ====
-==== ORDER BY Clause ====
-The ORDER BY clause returns selected rows in ascending or descending order by the specified column_spec or the ordinal position of a column_spec in the column list.
-  select trans_date, trans_amount from trans_table order by trans_date;
+==== WITH options ====
-  select trans_date, trans_amount from trans_table order by 2;
+== PRESERVE_CRITERIA ==
-The column_spec can be in the format of [database.][table.]column or table_alias.column.
+== PRESERVE_CRITERIA_ORDER ==
-  select trans_date, trans_amount from trans_table order by db1.trans_table.trans_amount;
+===== Examples =====
+==== Select all columns from table ====
-Column_spec can also be prefixed with an alias name of the table's column or an alias name of an expression.
+  > select * from mytable;
+==== Select with Omnidex criteria on Quicktext column ====
-  select trans_date, trans_amount - discount NetAmount from trans_table order by NetAmount;
+  > select company, contact from mytable where company = 'inform*';
+==== Aggregates, Functions, Expressions =====
-The ASC/DESC modifier can be used to return the data in ascending or descending order with ascending order being the default.
+  SELECT customer_no, sum(amount) FROM orders GROUP BY customer_no ORDER BY 2;
-  select trans_date, trans_amount from trans_table order by trans_date desc;
+  SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no ORDER BY 2, 1;
+==== Select List Item Ordinal Numbers ====
-==== WITH options ====
+  SELECT company, contact, state FROM customers ORDER BY 3;
-=== 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 = 'Information';
+  SELECT company, contact, state FROM customers ORDER BY 3, 1;
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

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

Sitemap Recent Changes