Differences

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

--- sql:intro [2009/07/17 05:37]
admin
+++ — (current)
@@ Line 1: / Line 1: @@
-====== Omnidex SQL Syntax ======
-Omnidex Queries are typically specified using an Omnidex SQL SELECT statement.
-===== Select List =====
-==== DISTINCT ====
-Distinct can be applied to an entire select-item list or used within an aggregate function.
-  SELECT DISTINCT city, state FROM prospects
-  SELECT count(DISTINCT zip_code) FROM prospects
-==== Result Set Limiters ====
-Result set limiters limit the rows returned by the select statement. The result set limiter processing is done AFTER the query has completed.
-TOP n, EVERY n, RANDOM n
-  SELECT TOP 10 company FROM prospects
-  SELECT EVERY 20 company FROM prospects
-  SELECT RANDOM 10 company FROM prospects
-These result set limiters do not order returned data in any way. Ordering is set by the ORDER BY clause.
-==== Column name(s) ====
-Column names from one or more tables.  These column names must be defined in the XML Catalog or the Environment Catalog.
-  SELECT company, contact, phone FROM customers
-  SELECT company, contact, order_date FROM customers, orders ...
-==== Alias Column Names ====
-An alias containing a space or special character must be quoted. Single or double quotes.
-The AS keyword is optional.
-  SELECT company CO, contact 'Contact Name', phone "Phone Number" FROM ...
-  SELECT contact AS 'Contact Name', phone AS 'Phone #' FROM ...
-==== Table qualified columns ====
-  SELECT customers.company, customers.contact, orders.order_date
-    FROM customers, orders ...
-==== Alias qualified columns ====
-  SELECT A.company, A.contact, B.order_date FROM company A, orders B ...
-==== Aggregate Functions ====
-^ Function ^ Description ^
-| min() | |
-| max() | |
-| avg() | |
-| sum() | |
-| count() | |
-| count(*) | |
-  SELECT customer_no, min(amount), max(amount), avg(amount)
-    FROM orders GROUP BY customer_no
-  SELECT company, sum(amount) FROM ...
-  SELECT count(*), count(state) FROM ...
-==== SQL92 Functions ====
-These functions can be used on literals or columns.
-^ Function ^ Description ^
-| CASE | |
-| CAST | |
-| CHARACTER_LENGTH | |
-| CHAR_LENGTH | |
-| Concatenation using %%||%% | Concatenates two strings together |
-| CURRENT_DATE | Current Date from the operating system |
-| CURRENT_TIME | Current Time from the operating system |
-| CURRENT_TIMESTAMP | Current Timestamp from the operating system |
-| CURRENT_USER | Current User from the operating system |
-| EXTRACT | Extract |
-| LOWER | Lower |
-| POSITION | Position |
-| SESSION_USER | Session User |
-| SUBSTRING | The substring of a string |
-| SYSTEM_USER | System User |
-| TRIM | Trim spaces from a string |
-| UPPER | Uppercase a string |
-| USER | User from the operating system |
-</code>
-  SELECT upper(company), upper(contact), current_date FROM ...
-  SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ...
-==== Extended Functions ====
-Extended Functions
-These functions can be used on literals or columns
-^ Function ^ Description ^
-| $COL_LEN | Return the length of a column as defined in the environment catalog. |
-| $COLUMN_LENGTH | Return the length of a column as defined in the environment catalog. |
-| [[sql:functions:contains|$CONTAINS]] | |
-| $CONTEXT | Return snippets of text from data qualified in a $CONTAINS function. |
-| $CONVERT | Convert a scalar expression from one data type to another. |
-| $CURRENT_ROW | Return the current row number. |
-| $EXTERNAL | Execute an external user-defined function. |
-| $IFNULL | Specify a return value for columns containing null values. |
-| $LJ | Left justify a string by eliminating leading white space. |
-| $LOOKUP | Retrieve textual metadata. |
-| $LPAD | Add leading "PAD" characters to a string. |
-| $MOD | Return "n modulus y" (remainder). |
-| $PROPER | Shift the first letter of each word in a string to upper case and all other letters to lower case. |
-| $RANDOM | Return a pseudo-random number. |
-| $RJ | Right justify a string by eliminating trailing white space and inserting leading spaces as needed. |
-| $ROUND | Round a numerical value to the specified number of decimal places. |
-| $RPAD | Add trailing "PAD" characters to a string. |
-| $SCORE | Returns the rank/relevancy score of qualified text from a $CONTAINS function. |
-| $SOUNDEX | Return the Soundex equivalent to a character string. |
-| $TRUNC | Return a numeric expression truncated to a specified number of digits to the right of the decimal point. |
-  SELECT $proper(company), $random(3) FROM ...
-  SELECT $soundex('database') FROM ...
-==== Literals ====
-  SELECT 'Company Name: ', company, 'Contact Name: ', contact FROM ...
-==== Expressions ====
-  SELECT order_number, 'Discount: ', (quantity * amount)*.02 FROM ...
-==== $ROWID, $ODXID ====
-$ROWID returns the internal rowid. This is either the native rowid or the redefined rowid.
-$ODXID is the Omnidex ID from the Omnidex indexes.
-  SELECT $ROWID, $ODXID, Company FROM ...
-==== * (Asterisk) ====
-  SELECT * FROM CUSTOMERS ...
-  SELECT c.*, o.* FROM CUSTOMERS c, ORDERS o ...
-==== Nested Select Statement ====
-  SELECT STATE, DESCRIPTION,
-    (SELECT SUM(TOTAL) FROM ORDERS WHERE TAX_STATE='CO')
-    FROM STATES WHERE STATE='CO'
-===== FROM Clause =====
-The FROM clause allows the following retrieval types and join operations:
-  * Single Table
-  * Multiple Tables (Joins)
-  * Inner Joins - Join occurs in the where clause
-  * Outer Joins - Left Outer Join, Right Outer Join
-  * Nested Selects
-==== Single Table ====
-  SELECT * FROM CUSTOMERS
-==== Multiple Tables (Joins) ====
-Join columns must have matching data types and lengths.
-  SELECT company, order_number FROM CUSTOMERS, ORDERS ...
-==== Inner Joins ====
-Tables are joined in the WHERE clause. The join predicate is 'AND'ed to other WHERE criteria predicates.
-  SELECT company, order_number, status FROM customers, orders WHERE
-     customers.customer_no = orders.customer_no AND orders.status='CANCELED'
-==== Outer Joins ====
-Outer joins prevent the exclusion of records in one table that do not have records in the joined table.
-  SELECT company, order_number, status FROM customers c LEFT JOIN orders o ON
-         c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c LEFT OUTER JOIN
-         orders o ON c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c RIGHT JOIN orders o ON
-         c.customer_no = o.customer_no
-  SELECT company, order_number, status FROM customers c RIGHT OUTER JOIN
-         orders o ON c.customer_no = o.customer_no
-==== Nested Selects ====
-  select company, contact, state from customers c,
-         (select distinct customer_no from orders where status='back') AS o
-         where c.customer_no = o.customer_no and state!='co'
-===== WHERE Clause =====
-$CONTAINS
-left_operand = right_operand (equal to)
-left_operand < right_operand (less than)
-left_operand > right_operand (greater than)
-left_operand <= right_operand (less than or equal to)
-left_operand >= right_operand (greater than or equal to)
-left_operand <> right_operand (not equal to)
-left_operand != right_operand (not equal to)
-left_operand in (value-list | Nested Select)
-left_operand like right_operand_with_wildcard
-exists (Nested Select) - MUST RETURN ONLY ONE COLUMN.
-=, <, >, <=, >=, <>, like - Nested Select that GUARANTEES to return ONLY ONE ROW. MUST RETURN ONLY ONE COLUMN.
-in (Nested Select) - Can return 0 or more rows. MUST RETURN ONLY ONE COLUMN.
-NOT, AND, OR with any of the above criteria predicates
-left_operand and right_operand can contain any of the following:
-    * Columns
-    * Table Qualified Columns - table.column
-    * Column alias
-    * Literals
-    * Constants
-    * Expressions
-    * Wildcards
-    * $VALUES, $ROWID, $ODXID
-    * Omnidex Sentinel Character
-    * IS NULL - This will not be an optimized query!
-    * Use parenthesis to force order of precedence.
-left_operand = right_operand (equal to)
-SELECT company, contact FROM customers where company='systems'
-SELECT customers.company, orders.order_no FROM customers, orders WHERE
-customers.customer_no=orders.customer_no AND (orders.status='shipped' OR
-orders.status='pending')
-left_operand < right_operand (less than)
-  SELECT company FROM customers WHERE customer_no < 100
-left_operand > right_operand (greater than)
-  SELECT customer_no, product_no FROM orders WHERE status='back' AND
-         (quantity*amount) > 50 ORDER BY 1, 2
-left_operand <= right_operand (less than or equal to)
-  SELECT * FROM inventory WHERE onhand <= 10
-left_operand >= right_operand (greater than or equal to)
-  SELECT company FROM customers WHERE customer_no < 100
-left_operand <> right_operand (not equal to)
-  SELECT company, state FROM customers WHERE state <> 'CO'
-left_operand != right_operand (not equal to)
-  SELECT company, state FROM customers WHERE state != 'CA'
-left_operand in (value-list | Nested Select)
-  SELECT company, state FROM customers WHERE state IN ('A?', 'C?')
-  SELECT company, state FROM customers WHERE state NOT IN ('AZ', 'CA')
-  SELECT * FROM orders WHERE total > (SELECT avg(total) FROM orders)
-left_operand like right_operand_with_wildcard
-  SELECT company, state FROM customers WHERE state like 'C?'
-  SELECT company, state FROM customers WHERE company = 'sys%'
-exists (Nested Select)
-Evaluates false if nested select returns 0 rows, true otherwise. In this example, all customer records will be returned.
-  SELECT c.company FROM customers c WHERE exists (SELECT * FROM orders o
-         WHERE o.status='back')
-=, <, >, <=, >=, <>, !=, like (Nested Select)
-These selects MUST return only one column and only one row.
-  SELECT * FROM orders WHERE customer_no = (SELECT customer_no FROM customers
-         WHERE company='Dynamic Information Systems')
-  SELECT customer_no, order_date, STATUS FROM orders WHERE product_no like
-        (SELECT product_no FROM products WHERE product_no='SUP16210')
-in (Nested Select)
-  SELECT distinct customer_no, status, product_no FROM orders WHERE product_no in
-         (SELECT product_no FROM products WHERE product_name='monitor')
-NOT, AND, OR
-The following two statements return identical results, but note the placement of the NOT operator.
-  SELECT company, state FROM customers WHERE company='%systems or software'
-         AND state NOT in ('AZ', 'CA')
-  SELECT company, state FROM customers WHERE company='%systems or software'
-         AND NOT state in ('AZ', 'CA')
-The next two statements return identical results, but note the differences in the WHERE clause with the Omnidex sentinel character and OR operator.
-  SELECT company, state FROM customers WHERE company='%systems or software'
-  SELECT company, state FROM customers WHERE company='systems' OR
-         company='software'
-The next two statements return companies located in Boulder CO, Boulder Creek CA, and Denton TX, but NOT Denver CO.
-  SELECT company, city, state FROM customers WHERE (city like 'boul%'
-         AND state in ('CO', 'CA')) OR (city like 'den%' AND state != 'CO')
-  SELECT company, city, state FROM customers WHERE (city like 'boul%'
-         AND state in ('CO', 'CA')) OR (city like 'den%' AND NOT state = 'CO')
-===== GROUP BY Clause =====
-Column Names from the Select List
-Column Names Not in the Select List
-HAVING Clause
-Column Names From the Select List
-  select customer_no, sum(amount) from orders group by customer_no
-  select city, state, count(distinct company) from customers group by city, state
-Column Names Not in the Select List
-  select status, sum(amount) from orders group by customer_no, status
-  select count(company) from customers group by state
-Having
-  select customer_no, sum(amount) from orders
-         group by customer_no having customer_no = 1001
-  select customer_no, sum(amount) from orders
-         group by customer_no having status != 'shipped'
-  select status, sum(amount) from orders group by status having customer_no
-         in (select customer_no from customers where company='systems')
-===== ORDER BY Clause =====
-Column Names
-Column Alias
-Select List Item Ordinal Numbers
-Aggregates, Functions, Expressions
-ASC, DESC
-Column Names
-  SELECT company, contact, phone FROM customers ORDER BY company
-  SELECT company, contact, phone FROM customers ORDER BY company, contact
-  SELECT company, contact, phone FROM customers ORDER BY state
-Column Alias
-  SELECT company CO, contact CT, phone PH FROM customers ORDER BY CO
-  SELECT company, contact, phone FROM customers ORDER BY CO, CT
-Select List Item Ordinal Numbers
-  SELECT company, contact, state FROM customers ORDER BY 3
-  SELECT company, contact, state FROM customers ORDER BY 3, 1
-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
-ASC, DESC
-DESC (Descending) sorts will NOT be optimized, meaning they may increase the overall elapsed query time. ASC (Ascending) sorts may be optimized, depending on the index installation.
-  SELECT company, contact, phone FROM customers ORDER BY company asc
-  SELECT customer_no, count(distinct order_no) FROM orders GROUP BY customer_no
-         ORDER BY 2 DESC, 1 ASC
-Each individual JDBC Statement object and ODBC CLI HSTMT objects are created on their own cursors. Likewise, each Connection object in both types of applications are created on their own instances. Therefore, the use of the ON CURSOR and ON INSTANCE clauses is unnecessary. To switch between multiple cursors or instances, simply reference the appropriate object.
-Syntax
-statement [ON [CURSOR] n | [INSTANCE] n] [WITH options]
-statement
-Required. Any valid SQL statement supported by Omnidex.
-ON
-===== ON CURSOR =====
-The ON CURSOR n clause is optional. If it is not specified, the SQL statement will be executed on the current cursor defined in the cursor parameter of the status array.
-Required if specifying the cursor or instance number. Omit if using the default current cursor or instance.
-[CURSOR | INSTANCE]
-Optional. The CURSOR and INSTANCE keywords are optional.
-n
-Required if specifying the cursor or instance number. Omit if using the default current cursor or instance. This is the cursor or instance number that the current command will be executed on. Cursors and instances are numbered automatically as they are created. In OdxSQL, use the CURSORDISPLAY ON set option to automatically display the current cursor and instance numbers.
-[WITH options]
-Optional. A comma separated list of options to use for the current statement.
-ODXSQL Example
-<code>
->SET CURSORDISPLAY ON
->connect /users/garment.env
-Connected to /users/garment.env on instance 1, cursor 1
->connect / users/orders.env
-Connected to /users/orders.env on instance 2, cursor 2
-SELECT COMPANY, CONTACT, PHONE FROM CUSTOMERS WHERE STATE='CO' ON CURSOR 2
-...
-INSERT INTO CUSTOMERS (COMPANY, CONTACT) VALUES ('New Wave', 'Mr. Smith') ON CURSOR 1
-</code>
-===== ON INSTANCE =====
-The ON INSTANCE n clause can only be used by statements that do not require a cursor to be open. This includes statements like INSERT, INSERTINDEX, and many more. See the individual statement definitions for information on whether the statement uses an instance or a cursor. If omitted, the SQL statement will be executed on the instance owning the cursor passed in the cursor parameter of oaexecsql or in the instance member of the status array.
-=====  Criteria Conditioning =====
-Rules for handling criteria strings in a WHERE clause:
-Rule 1 - Sentinel Character
-The Omnidex Sentinel character is used in the right operand of a WHERE clause predicate and instructs Omnidex to consider the following criteria as Omnidex specific, as opposed to SQL specific. For example, company = '% systems or software' is the same as company = 'systems' or company = 'software'.
-The Omnidex Sentinel character is determined by the ENVIRONMENT ...OMNIDEX SENTINEL clause in the environment catalog. The default is %.
-If the sentinel character is the leading character in the string and the predicate operator is = or IN, the criteria, minus the sentinel character, are submitted directly to Omnidex.
-Rule 2 - Special Characters
-Fields installed with the KW (Keyword) option will be processed as follows:
-Leading and trailing characters that would be removed during indexing, are removed from the criteria string.
-When using the =, !=, LIKE and IN operators, embedded characters that are treated as keyword delimiters during indexing, are changed to a space. The space is the implied AND operator within Omnidex which enables a multiple-keyword search. For each keyword, leading and trailing characters that would be removed during indexing are removed and each keyword is processed individually according to the other rules.
-When using the >, <, >=, <=, and BETWEEN operators, the leading characters that are removed during indexing are removed. Criteria containing multiple keywords are quoted and regarded as a single keyword. Multiple-keyword searches are not supported with these operators.
-Rule 3 - Quoted Operators
-Characters that are regarded as operators by the underlying indexing routines, are treated as literals and quoted as needed. Reserved words like AND, OR, NOT and TO are also quoted.
-Rule 4 - Wildcard Characters
-When the criteria contains a wildcard character (default *, ?, #) and is used with the >, <, >=, <= and BETWEEN operators, the criteria are quoted, causing the wildcard characters to be treated as literal characters. Wildcards are not supported with these operators.
-Rule 5 - % and _ (Percent and underscore) Characters
-When used with the LIKE operator, the % and _ characters are ANSI SQL wildcard characters and are therefore converted to the corresponding Omnidex wildcard characters * and ? (or the corresponding custom wildcard characters as defined in the environment catalog).
-When used with = (equals), % is the default Omnidex Sentinel character.
-They are treated as literal characters when used with any other operator.
-Rule 6 - Blank Criteria
-Criteria consisting of spaces or an empty string, is changed to a single space and quoted.
-Rule 7 - Double Quotes
-Double-quotes tell Omnidex to treat characters as literals. Therefore:
-When all or part of the criteria string is double-quoted, Omnidex will process ANSI wildcards according to rule 5, provided the wildcards are outside of the quotes.
-Omnidex will not process special characters according to rules 3 and 4.
-Rule 8 - $SOUNDEX
-If the criteria is from the $SOUNDEX function and the index being used has been installed with the ;SX option, the indexing token '!' will be appended to the original string from the $SOUNDEX function. The presence of this token will cause the indexing layer to process a Soundex search
-====== Quick Links ======
-^    [[:start|Quick Links]]  ^^^^^
-^   General     ^   Programs              ^       Interfaces       ^   General Topics       ^   Miscellaneous Items^
-| [[overview:intro|Omnidex Introduction]] | [[programs:odxsql:intro|OdxSQL]] |[[development:interfaces:odbc:intro|ODBC]] | [[design:grids:intro|Grids]] | [[releasenotes:intro|Release Notes]] |
-| [[odxadmin:intro|Administrating Omnidex (OdxAdmin)]]     | [[programs:odxnet:intro|OdxNet]] |[[development:interfaces:jdbc:intro|JDBC]] | [[design:unionview:intro|Union Views]]| [[osinstall:intro|Server Installs]] |
-| [[design:intro|Data and Index Design]]         | [[programs:odxaim:intro|OdxAim]] |[[development:interfaces:storedproc:intro|Stored Procedures]] | [[design:rolluptable:intro|Rollup Tables]] | [[osinstall:licensing:intro|Licensing]] |
-| [[sql:intro|Omnidex Queries & SQL]]       | [[programs:dbinstal:intro|DBInstal]]   |[[development:interfaces:omniaccess:intro|OmniAccess]] | [[design:partitioning:intro|Partitioning]] | [[osinstall:settings:intro|Settings]] |
-| [[activecounts:intro|Active Counts]] | [[programs:oaenv:intro|OaComp/decomp/helper]]   |[[development:interfaces:clientoa:intro|Client OA]] | [[development:explainplan:intro|Explain Plans]]| [[glossary:intro|Glossary]] |
-| [[powersearch:intro|PowerSearch]] | [[programs:client:intro|DSEdit/OdxQuery]]  | [[rdbms:intro|RDBMS Indexing]] | [[development:debugging:intro|Debugging]]  | [[appendix:intro|Appendix]] |
-(c) Copyright Dynamic Information Systems - This document was last updated July 15, 2009.
-----

Back to top

sql/intro.1247809041.txt.gz · Last modified: 2012/10/26 14:21 (external edit)

Sitemap Recent Changes