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

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'

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

SELECT * FROM CUSTOMERS

Join columns must have matching data types and lengths.

SELECT company, order_number FROM CUSTOMERS, ORDERS ...

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 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

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'

$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')

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')

© Copyright Dynamic Information Systems - This document was last updated July 15, 2009.