This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
dev:sql:examples:home [2009/12/03 20:26] 127.0.0.1 external edit |
dev:sql:examples:home [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
{{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
- | <html><div align="center"><span style="color:red">DRAFT</span></div></html> | ||
====== Omnidex SQL Examples ====== | ====== Omnidex SQL Examples ====== | ||
- | ^ [[ dev:sql:overview | SQL Overview ]] ^ [[ dev:sql:statements:home | SQL Statements ]] ^ [[dev:sql:functions:home | SQL Functions ]] ^ | + | {{page>:sql_bar&nofooter&noeditbtn}} |
+ | ===== $CONTAIN Function ===== | ||
- | ===== SELECT ===== | + | SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,'fred,'SYNONYMS=ALL_GIVEN_NAMES'); |
- | ===== Functions ===== | + | |
- | 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'); | ||
- | SELECT FIRST_NAME FROM CUSTOMERS WHERE \ | + | SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME, |
- | $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=ALL_GIVEN_NAMES') | + | '(SYNONYMS(fred,"ALL_GIVEN_NAMES"))'); |
+ | |||
+ | ===== Qualify Statement ===== | ||
QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")' | QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")' | ||
Line 16: | Line 18: | ||
QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,,"LIST=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 ==== | + | ===== Searches ===== |
- | + | Example 1: Basic search | |
- | $contains(order_date, '1st_qtr_1990', 'synonyms=time_periods,exclusive') | + | |
<code> | <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, \ | select TOP 20 PRO.COMPANY, \ | ||
PRO.CONTACT, \ | PRO.CONTACT, \ | ||
Line 141: | Line 37: | ||
</code> | </code> | ||
- | ; Example 2: Same select list, but use $contains to allow synonyms | + | Example 2: Same select list, but use $contains to allow synonyms |
- | <code sql> | + | <code> |
select TOP 20 PRO.COMPANY, \ | select TOP 20 PRO.COMPANY, \ | ||
PRO.CONTACT, \ | PRO.CONTACT, \ | ||
Line 157: | Line 53: | ||
$contains(PRO.CUST_SINCE, '1996') | $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. | ||
+ | |||
<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, \ | select TOP 20 PRO.COMPANY, \ | ||
PRO.CONTACT, \ | PRO.CONTACT, \ | ||
Line 179: | Line 71: | ||
$contains(MFRS.DESCRIPTION, 'Dell') and \ | $contains(MFRS.DESCRIPTION, 'Dell') and \ | ||
$contains(INC.DESCRIPTION, '$50,000-$59,999') | $contains(INC.DESCRIPTION, '$50,000-$59,999') | ||
- | </code? | + | </code> |
+ | Crosstab | ||
<code> | <code> | ||
- | ;============================================================================== | ||
- | ; | ||
- | ; CROSSTAB VIEW | ||
- | ; | ||
- | ; | ||
- | ============================================================================== | ||
- | ; Example 1: List View Example #1 criteria shown with a crosstab of counts by | + | <code> |
- | ; product category and fiscal quarter | + | |
- | </code> | + | |
- | <code sql> | + | |
select CAT.DESCRIPTION, \ | select CAT.DESCRIPTION, \ | ||
ORD.OR_FQTR, \ | ORD.OR_FQTR, \ | ||
Line 204: | Line 88: | ||
group by CAT.DESCRIPTION, \ | group by CAT.DESCRIPTION, \ | ||
ORD.OR_FQTR | ORD.OR_FQTR | ||
- | <code> | + | </code> |
| | ||
- | ; Example 2: List View Example #2 criteria shown with a crosstab of totals by | + | Example 2: List View Example #2 criteria shown with a crosstab of totals by income and profession |
- | ; income and profession | + | |
- | <code SQL> | + | <code> |
select INC.DESCRIPTION, \ | select INC.DESCRIPTION, \ | ||
PRF.DESCRIPTION, \ | PRF.DESCRIPTION, \ | ||
Line 225: | Line 108: | ||
===== criteria shown with a crosstab of counts by cust_since and region ===== | ===== criteria shown with a crosstab of counts by cust_since and region ===== | ||
- | <code SQL> | + | <code> |
select ORD.CUST_SINCE, \ | select ORD.CUST_SINCE, \ | ||
ORD.REGION, \ | ORD.REGION, \ |