Differences

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] (current)
@@ 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');
-===== Functions =====
+  SELECT FIRST_NAME FROM CUSTOMERS WHERE
+      $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=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,
+     '(SYNONYMS(fred,"ALL_GIVEN_NAMES"))');
-  SELECT FIRST_NAME FROM CUSTOMERS WHERE \
+===== Qualify Statement =====
-      $CONTAINS(FIRST_NAME,'fred,'SYNONYMS','LIST=ALL_GIVEN_NAMES')
-===== Qualify =====
   QUALIFY CUSTOMERS WHERE FIRST_NAME = 'SYNONYMS(fred,"ALL_GIVEN_NAMES")'
@@ Line 18: / Line 18: @@
   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>
-;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, \
@@ Line 143: / Line 37: @@
 </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, \
               PRO.CONTACT, \
@@ Line 159: / Line 53: @@
               $contains(PRO.CUST_SINCE, '1996')
 </code>
+Example 3: Same select list, but use joins to more dimensions to allow description fields from other dimensions and snowflakes.
 <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, \
@@ Line 181: / Line 71: @@
               $contains(MFRS.DESCRIPTION, 'Dell') and \
               $contains(INC.DESCRIPTION, '$50,000-$59,999')
-</code?
+</code>
+Crosstab
 <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, \
               ORD.OR_FQTR, \
@@ Line 206: / Line 88: @@
   group by    CAT.DESCRIPTION, \
               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, \
               PRF.DESCRIPTION, \
@@ Line 227: / Line 108: @@
 ===== criteria shown with a crosstab of counts by cust_since and region =====
-<code SQL>
+<code>
 select        ORD.CUST_SINCE, \
               ORD.REGION, \

Back to top

dev/sql/examples/home.1278052752.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes