This is an old revision of the document!
Omnidex Queries are typically specified using an Omnidex SQL SELECT statement.
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 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 names from one or more tables.
SELECT company, contact, phone FROM customers
SELECT company, contact, order_date FROM customers, orders ...
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 ...
SELECT customers.company, customers.contact, orders.order_date FROM customers, orders ...
SELECT A.company, A.contact, B.order_date FROM company A, orders B ...
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 ...
These functions can be used on literals or columns.
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
SELECT upper(company), upper(contact), current_date FROM ...
SELECT position('SYS' IN 'DYNAMIC INFORMATION SYSTEMS'), system_user FROM ...
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. |
| $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 ...
SELECT 'Company Name: ', company, 'Contact Name: ', contact FROM ...
SELECT order_number, 'Discount: ', (quantity * amount)*.02 FROM ...
$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 ...
SELECT * FROM CUSTOMERS ...
SELECT c.*, o.* FROM CUSTOMERS c, ORDERS o ...
SELECT STATE, DESCRIPTION, (SELECT SUM(TOTAL) FROM ORDERS WHERE TAX_STATE='CO') FROM STATES WHERE STATE='CO'
© Copyright Dynamic Information Systems - This document was last updated July 15, 2009.