Re: Complex Query

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 11 May 2009 13:08:38 -0700 (PDT)
Message-ID: <c30f43be-fb1f-4dd0-9698-743e64b51721_at_g20g2000vba.googlegroups.com>



On May 11, 9:41 am, a..._at_unsu.com wrote:
> 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........- Hide quoted text -
>
> - Show quoted text -

I do not have time to study the query in detail but why are you using the one letter 'p' to alias two different occurrences of the product table? Between scope rules and query transformation this could lead to issues.

I you can post the create table and insert of a few rows that will duplicate the results someone may have time to investigate what is happening.

  • Mark D Powell --
Received on Mon May 11 2009 - 15:08:38 CDT

Original text of this message