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 -
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