This is an old revision of the document!


DRAFT

Omnidex SQL Examples

$CONTAIN Function

SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,'fred,'SYNONYMS=ALL_GIVEN_NAMES')
SELECT FIRST_NAME FROM CUSTOMERS WHERE \
    $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=ALL_GIVEN_NAMES')

Qualify Statement

QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")'
QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,,"LIST=ALL_GIVEN_NAMES")'
SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME, \
   '(SYNONYMS(fred,"ALL_GIVEN_NAMES"))') 

Searches

Example 1: Basic search

select        TOP 20 PRO.COMPANY, \
              PRO.CONTACT, \
              PRD.DESCRIPTION, \
              ORD.ORDER_DATE, \
              ORD.QUANTITY, \
              ORD.AMOUNT, \
              ORD.TOTAL \
  from        ORDERS_SVW ORD \
  join        PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
  join        PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
  where       ORD.ORDER_DATE BETWEEN 'Jan 1 2000' and 'Mar 31 2000' and \
              PRO.STATE = 'CO' and \
              PRO.CUST_SINCE = '1996'

Example 2: Same select list, but use $contains to allow synonyms

select        TOP 20 PRO.COMPANY, \
              PRO.CONTACT, \
              PRD.DESCRIPTION, \
              ORD.ORDER_DATE, \
              ORD.QUANTITY, \
              ORD.AMOUNT, \
              ORD.TOTAL \
  from        ORDERS_SVW ORD \
  join        PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
  join        PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
  where       $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
              $contains(PRO.STATE, 'COLORADO', 'synonyms=state_codes') and \
              $contains(PRO.CUST_SINCE, '1996')

Example 3: Same select list, but use joins to more dimensions to allow description fields from other dimensions and snowflakes.

select        TOP 20 PRO.COMPANY, \
              PRO.CONTACT, \
              PRD.DESCRIPTION, \
              ORD.ORDER_DATE, \
              ORD.QUANTITY, \
              ORD.AMOUNT, \
              ORD.TOTAL \
  from        ORDERS_SVW ORD \
  join        PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
  join        PRODUCTS PRD on ORD.PRODUCT_NO = PRD.PRODUCT_NO \
  join        INCOMES INC on PRO.INCOME = INC.INCOME \
  join        MFRS on PRD.MFR = MFRS.MFR \
  where       $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
              $contains(MFRS.DESCRIPTION, 'Dell') and \
              $contains(INC.DESCRIPTION, '$50,000-$59,999') 

Crosstab

<code>
select        CAT.DESCRIPTION, \
              ORD.OR_FQTR, \
              COUNT(ORD.TOTAL) \
  from        ORDERS_SVW ORD \
  join        PROSPECTS PRO on ORD.ACCT = PROSPECTS.ACCT \
  join        CATEGORIES CAT on ORD.CATEGORY = CAT.CATEGORY \
  where       ORD.ORDER_DATE BETWEEN 'Jan 1 2000' and 'Mar 31 2000' and \
              PRO.STATE = 'CO' and \
              PRO.CUST_SINCE = '1996' \
  group by    CAT.DESCRIPTION, \
              ORD.OR_FQTR

Example 2: List View Example #2 criteria shown with a crosstab of totals by income and profession

select        INC.DESCRIPTION, \
              PRF.DESCRIPTION, \
              SUM(ORD.TOTAL) \
  from        ORDERS_SVW ORD \
  join        PROSPECTS PRO on ORD.ACCT = PRO.ACCT \
  join        INCOMES INC on ORD.INCOME = INC.INCOME \
  join        PROFESSIONS PRF on ORD.PROFESSION = PRF.PROFESSION \
  where       $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') and \
              $contains(PRO.STATE, 'COLORADO', 'synonyms=state_codes') and \
              $contains(PRO.CUST_SINCE, '1996') \
  group by    INC.DESCRIPTION, \
              PRF.DESCRIPTION

criteria shown with a crosstab of counts by cust_since and region

SELECT        ORD.CUST_SINCE, \
              ORD.REGION, \
              COUNT(ORD.TOTAL) \
  FROM        ORDERS_SVW ORD \
  JOIN        PROSPECTS PRO ON ORD.ACCT = PRO.ACCT \
  JOIN        PRODUCTS PRD ON ORD.PRODUCT_NO = PRD.PRODUCT_NO \
  JOIN        INCOMES INC ON PRO.INCOME = INC.INCOME \
  JOIN        MFRS ON PRD.MFR = MFRS.MFR \
  WHERE       $contains(ORD.ORDER_DATE, '1ST_QTR_2000', 'synonyms=time_periods, exclusive') AND \
              $contains(MFRS.DESCRIPTION, 'Dell') AND \
              $contains(INC.DESCRIPTION, '$50,000-$59,999') \
  GROUP BY    ORD.CUST_SINCE, \
              ORD.REGION
 
Back to top
dev/sql/examples/home.1278053132.txt.gz ยท Last modified: 2016/06/28 22:38 (external edit)