This shows you the differences between two versions of the page.
dev:sql:examples:home [2010/07/02 06:39] tdo |
dev:sql:examples:home [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
- | ====== Omnidex SQL Examples ====== | ||
- | {{page>:sql_bar&nofooter&noeditbtn}} | ||
- | ===== $CONTAIN Function ===== | ||
- | 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 ===== | ||
- | |||
- | 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 ==== | ||
- | |||
- | $contains(order_date, '1st_qtr_1990', 'synonyms=time_periods,exclusive') | ||
- | <code> | ||
- | ;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' | ||
- | </code> | ||
- | |||
- | ; Example 2: Same select list, but use $contains to allow synonyms | ||
- | <code sql> | ||
- | 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') | ||
- | </code> | ||
- | <code> | ||
- | ; 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. | ||
- | </code> | ||
- | <code sql> | ||
- | 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 | ||
- | </code> | ||
- | <code sql> | ||
- | 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 | ||
- | </code> | ||
- | ===== criteria shown with a crosstab of counts by cust_since and region ===== | ||
- | |||
- | <code SQL> | ||
- | 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 | ||
- | </code> | ||
- | |||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |