Omnidex SQL Examples

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

$contains(order_date, '1st_qtr_1990', 'synonyms=time_periods,exclusive')   

;set autoexplain on
set explain counts, text, $ox
set pagelength 0
set timer on

connect star


;==============================================================================
;
; DESIGN - Prompts
;
; These samples presume the following prompts on the screen:
;
;   Time
;     - Dates or Date Ranges, supported by synonyms for things like 
;       THIS_MONTH, LAST_QUARTER, etc.  Always try to encourage a time filter.
;
;   Company
;     - Company Name
;     - Region, State, Zip
;
;   Personal Demographics
;     - Income
;     - Profession
;     - Cust Since
;
;   Product Info
;     - Product Name
;     - Product Category
;     - Manufacturer
;
;
;
;                                                          Counts 
;   Time Period:      [THIS_MONTH]                        [_____]  
;   Company:          [_______________________________]   [_____]
;   Region:           [__]   State: [__]   Zip: [_____]   [_____]
;   Profession:       [_______________________________]   [_____]
;   Income:           [_______________________________]   [_____]
;   Cust Since:       [____]                              [_____]
;   Product Name:     [_______________________________]   [_____]
;   Product Category: [_______________________________]   [_____]
;   Manufacturer:     [_______________________________]   [_____]
;
;
;------------------------------------------------------------------------------
;
; DESIGN - Lists
;
; These samples presume the following list view:
;
;
; [Company] [Contact]  [Product]  [Date]  [Quantity]  [Amount]  [Tax]  [Total]
;
;
; There is no restriction on the fields that can be shown in the list view. It
; is possible to include any field from ORDERS_SVW, PROSPECTS, PRODUCTS, or 
; any of the other tables that join to ORDERS_SVW.  
;
; 
;------------------------------------------------------------------------------
;
; DESIGN - Crosstab
;
; For simplicity, the crosstab always summaries either counts or the TOTAL 
; column (the result of product cost times quantity, not including sales tax.  
; These samples presume the following crosstab choices:
;
;   X Axis:
;
;     Region    
;     State     
;     Zip
;     Income
;     Profession
;     Cust Since
;     Product Name
;     Product Category
;     Manufacturer
;
;  Y Axis 
;  (Shorter list to avoid too much scrolling to the right)
;     
;     Fiscal Year
;     Fiscal Quarter
;     Region      
;     Income 
;     Profession
;     Cust Since
;
;==============================================================================

;
;==============================================================================
;
; LIST VIEW
;
;==============================================================================

; Example 1: Basic search
</code?
<code slq>
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.  The
;            joins are added on an 'as needed' basis depending on whether
;            a particular prompt was used.

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') 
</code?
 
<code>
;==============================================================================
;
; CROSSTAB VIEW
;
;
==============================================================================
 
; Example 1: List VIEW Example #1 criteria shown WITH a crosstab OF counts BY 
;            product category AND fiscal quarter

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
<code>
 
  ; Example 2: List VIEW Example #2 criteria shown WITH a crosstab OF totals BY 
  ;            income AND profession
 
<code SQL>
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