Differences

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

--- dev:sql:examples:overview [2009/11/30 17:31]
tdo
+++ — (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 ======
-===== SELECT =====
-===== Functions =====
-==== $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}}

Back to top

dev/sql/examples/overview.1259602280.txt.gz · Last modified: 2012/10/26 14:27 (external edit)

Sitemap Recent Changes