Omnidex SQL: Examples

$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

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

;

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

; 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