Re: Complex Query

From: <art_at_unsu.com>
Date: Mon, 11 May 2009 06:41:39 -0700 (PDT)
Message-ID: <0109b5e8-f75b-4939-9205-12677429efbf_at_z7g2000vbh.googlegroups.com>

On May 8, 6:28 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On May 8, 6:30 pm, Shakespeare <what..._at_xs4all.nl> wrote:
>
>
>
> > a..._at_unsu.com schreef:
>
> > > I am trying to make a query with virtual tables to apply 2 different
> > > sets of criteria and get 2 columns from 1 table.  It almost works.
> > > But if you look at the query, the issue is that the column ORDER_DATE
> > > appears in both virtual tables, so I get an error.
>
> > > But it is the ORDER_DATE which is the key here, as I want one date per
> > > line with the different figures:
>
> > > SELECT order_date, trials, annuals
> > > FROM (SELECT order_date, trials
> > >       FROM (SELECT TRUNC(order_date) order_date, COUNT(*)
> > >             OVER (PARTITION BY TRUNC(order_date)) trials
> > >             FROM engine.customer_order co, engine.order_line ol,
> > > engine.product p
> > >             WHERE co.order_id     = ol.order_id
> > >               AND ol.product_id   = p.product_id
> > >               AND p.subproduct_id = 197
> > >               AND confirm         = 'Trial2Paid'
> > >               AND order_date >= TO_DATE('03012009','MMDDYYYY'))),
> > >       (SELECT order_date, annuals
> > >        FROM (SELECT TRUNC(order_date) order_date, COUNT(*)
> > >              OVER (PARTITION BY TRUNC(order_date)) annuals
> > >              FROM engine.customer_order co, engine.order_line ol,
> > > engine.product p
> > >              WHERE co.order_id     = ol.order_id
> > >               AND ol.product_id    = p.product_id
> > >               AND p.subproduct_id  = 197
> > >               AND confirm          = 'AutoRenew'
> > >               AND order_date >= TO_DATE('03012009','MMDDYYYY')
> > >               AND ((p.quantity = 12 AND p.quantity_uom = 'month') OR
> > > (p.quantity = 1 AND p.quantity_uom = 'year'))))
>
> > Since you claim to be a senior DBA, you should know how to alias your
> > tables and columns and how to prefix your columns with the table
> > aliases.....
>
> > Shakespeare- Hide quoted text -
>
> > - Show quoted text -
>
> Shakespeare coud have said it nicer but he is right in that all you
> should need to do is prefix your column list.
>
> HTH -- Mark D Powell --

Well, the issue is with that 'order date' column. Seems if I use it in my order by, depending on which one I use, it changes the output of the query. So, I know I can prefix the column. That has nothing to do with my question. See what I've done and what my output is:

If I change the query to this, and use PARTITION BY order_date_1 in line #3, I'll get the following output, which is wrong for the 'annual' column:

SELECT order_date_1, trials, order_date_2, annuals, rnum FROM (SELECT order_date_1, trials, order_date_2, annuals, ROW_NUMBER()

      OVER (PARTITION BY order_date_1 ORDER BY order_date_1) rnum
      FROM (SELECT order_date_1, trials
            FROM (SELECT TRUNC(order_date) order_date_1, COUNT(*)
                  OVER (PARTITION BY TRUNC(order_date)) trials
                  FROM engine.customer_order co, engine.order_line ol,
engine.product p
                  WHERE co.order_id     = ol.order_id
                    AND ol.product_id   = p.product_id
                    AND p.subproduct_id = 197
                    AND confirm         = 'Trial2Paid'
                    AND order_date >= TO_DATE
('03012009','MMDDYYYY'))),
            (SELECT order_date_2, annuals
             FROM (SELECT TRUNC(order_date) order_date_2, COUNT(*)
                   OVER (PARTITION BY TRUNC(order_date)) annuals
                   FROM engine.customer_order co, engine.order_line
ol, engine.product p
                   WHERE co.order_id     = ol.order_id
                    AND ol.product_id    = p.product_id
                    AND p.subproduct_id  = 197
                    AND confirm          = 'AutoRenew'
                    AND order_date >= TO_DATE('03012009','MMDDYYYY')
                    AND ((p.quantity = 12 AND p.quantity_uom =

'month') OR (p.quantity = 1 AND p.quantity_uom = 'year'))))) WHERE rnum = 1;

ORDER_DAT TRIALS ORDER_DAT ANNUALS RNUM

--------- ---------- --------- ---------- ----------
01-MAR-09         55 01-MAR-09          2          1
02-MAR-09         20 01-MAR-09          2          1
03-MAR-09        103 01-MAR-09          2          1
04-MAR-09         19 01-MAR-09          2          1
05-MAR-09         17 01-MAR-09          2          1
06-MAR-09         13 01-MAR-09          2          1
07-MAR-09          2 01-MAR-09          2          1
08-MAR-09          9 01-MAR-09          2          1

If I change the PARTITION BY and use order_date_2 in row #3, I get the opposite effect:

SELECT order_date_1, trials, order_date_2, annuals, rnum FROM (SELECT order_date_1, trials, order_date_2, annuals, ROW_NUMBER()

      OVER (PARTITION BY order_date_2 ORDER BY order_date_2) rnum
      FROM (SELECT order_date_1, trials
            FROM (SELECT TRUNC(order_date) order_date_1, COUNT(*)
                  OVER (PARTITION BY TRUNC(order_date)) trials
                  FROM engine.customer_order co, engine.order_line ol,
engine.product p
                  WHERE co.order_id     = ol.order_id
                    AND ol.product_id   = p.product_id
                    AND p.subproduct_id = 197
                    AND confirm         = 'Trial2Paid'
                    AND order_date >= TO_DATE
('03012009','MMDDYYYY'))),
            (SELECT order_date_2, annuals
             FROM (SELECT TRUNC(order_date) order_date_2, COUNT(*)
                   OVER (PARTITION BY TRUNC(order_date)) annuals
                   FROM engine.customer_order co, engine.order_line
ol, engine.product p
                   WHERE co.order_id     = ol.order_id
                    AND ol.product_id    = p.product_id
                    AND p.subproduct_id  = 197
                    AND confirm          = 'AutoRenew'
                    AND order_date >= TO_DATE('03012009','MMDDYYYY')
                    AND ((p.quantity = 12 AND p.quantity_uom =

'month') OR (p.quantity = 1 AND p.quantity_uom = 'year'))))) WHERE rnum = 1;

ORDER_DAT TRIALS ORDER_DAT ANNUALS RNUM

--------- ---------- --------- ---------- ----------
01-MAR-09         55 01-MAR-09          2          1
01-MAR-09         55 02-MAR-09          2          1
01-MAR-09         55 04-MAR-09          3          1
01-MAR-09         55 05-MAR-09          3          1
01-MAR-09         55 06-MAR-09          1          1
01-MAR-09         55 07-MAR-09          3          1
01-MAR-09         55 08-MAR-09          5          1
01-MAR-09         55 09-MAR-09          3          1

So, I'm still perplexed on how to do this. I'm sure it is possible, just have to find the right subqueries that will do it........ Received on Mon May 11 2009 - 08:41:39 CDT