Re: Complex Query

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 8 May 2009 16:28:24 -0700 (PDT)
Message-ID: <9c7720d5-1caa-47cb-b209-b0115ba923c4_at_q14g2000vbn.googlegroups.com>



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 -- Received on Fri May 08 2009 - 18:28:24 CDT

Original text of this message