This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
sql:intro [2009/07/16 22:45] admin |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Omnidex Queries and SQL Syntax ====== | ||
| - | |||
| - | ===== Clauses ===== | ||
| - | |||
| - | ==== 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. | ||
| - | |||
| - | SELECT company, contact, phone FROM customers | ||
| - | |||
| - | SELECT company, contact, order_date FROM customers, orders ... | ||
| - | |||
| - | |||
| - | |||
| - | ==== Column name(s) with an alias ==== | ||
| - | |||
| - | 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 column name(s) ==== | ||
| - | |||
| - | |||
| - | SELECT customers.company, customers.contact, orders.order_date | ||
| - | FROM customers, orders ... | ||
| - | |||
| - | ==== Table-alias qualified column name(s) ==== | ||
| - | |||
| - | |||
| - | SELECT A.company, A.contact, B.order_date FROM company A, orders B ... | ||
| - | |||
| - | |||
| - | |||
| - | ==== Aggregate Functions ==== | ||
| - | |||
| - | 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-Compliant Functions ==== | ||
| - | |||
| - | These functions can be used on literals or columns. | ||
| - | <code> | ||
| - | CASE | ||
| - | CAST | ||
| - | CHARACTER_LENGTH | CHAR_LENGTH | ||
| - | Concatenation using || | ||
| - | CURRENT_DATE | ||
| - | CURRENT_TIME | ||
| - | CURRENT_TIMESTAMP | ||
| - | CURRENT_USER | ||
| - | EXTRACT | ||
| - | LOWER | ||
| - | POSITION | ||
| - | SESSION_USER | ||
| - | SUBSTRING | ||
| - | SYSTEM_USER | ||
| - | TRIM | ||
| - | UPPER | ||
| - | USER | ||
| - | </code> | ||
| - | SELECT upper(company), upper(contact), current_date FROM ... | ||
| - | |||
| - | SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ... | ||
| - | |||
| - | |||
| - | |||
| - | ==== Omnidex Extended Functions ==== | ||
| - | |||
| - | Extended Functions | ||
| - | |||
| - | ^ 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. | | ||
| - | | $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. | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | These functions can be used on literals or columns | ||
| - | <code> | ||
| - | $COLUMN_LENGTH | $COL_LEN | ||
| - | $CONVERT | ||
| - | $CURRENT_ROW | ||
| - | $EXTERNAL | ||
| - | $IFNULL | ||
| - | $LJ | ||
| - | $LPAD | ||
| - | $MOD | ||
| - | $PROPER | ||
| - | $RANDOM | ||
| - | $RJ | ||
| - | $ROUND | ||
| - | $RPAD | ||
| - | $SOUNDEX | ||
| - | $TRUNC | ||
| - | </code> | ||
| - | 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' | ||
| - | |||
| - | |||
| - | |||
| - | ====== 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. | ||
| - | |||
| - | ---- | ||