Re: Complex Query

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 09 May 2009 00:30:48 +0200
Message-ID: <4a04b29f$0$188$e4fe514c_at_news.xs4all.nl>

art_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 Received on Fri May 08 2009 - 17:30:48 CDT