This is an old revision of the document!
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