Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
dev:sql:examples:home [2010/07/02 06:39]
tdo
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 ======
 {{page>:​sql_bar&​nofooter&​noeditbtn}} {{page>:​sql_bar&​nofooter&​noeditbtn}}
 ===== $CONTAIN Function ===== ===== $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=ALL_GIVEN_NAMES'​);
  
-  SELECT FIRST_NAME FROM CUSTOMERS WHERE \ +  SELECT FIRST_NAME FROM CUSTOMERS WHERE  
-      $CONTAINS(FIRST_NAME,'​fred,'​SYNONYMS','​LIST=ALL_GIVEN_NAMES'​)+      $CONTAINS(FIRST_NAME,'​fred,'​SYNONYMS','​LIST=ALL_GIVEN_NAMES'​);
  
-===== Qualify =====+  SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,​  
 +     '​(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, \
 
Back to top
dev/sql/examples/home.1278052780.txt.gz · Last modified: 2016/06/28 22:38 (external edit)