Differences

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

Link to this comparison view

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  
 +      $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=ALL_GIVEN_NAMES'); 
 + 
 +  SELECT FIRST_NAME FROM CUSTOMERS WHERE $CONTAINS(FIRST_NAME,  
 +     '(SYNONYMS(fred,"ALL_GIVEN_NAMES"))'); 
  
-  SELECT FIRST_NAME FROM CUSTOMERS WHERE \ 
-      $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=ALL_GIVEN_NAMES') 
 ===== Qualify Statement ===== ===== Qualify Statement =====
  
Line 15: 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 140: 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 156: 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 178: 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 203: 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 224: 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.1278052795.txt.gz · Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0