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