Omnidex SQL Examples

  os> odxsql
  odxsql> connect myenv.xml;
  odxsql> EXTRACT DDL to myfile.sql;

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

SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME, 
   '(SYNONYMS(fred,"ALL_GIVEN_NAMES"))'); 

QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")'

QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,,"LIST=ALL_GIVEN_NAMES")'

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

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

select * from $abort;