Complex Query
From: <art_at_unsu.com>
Date: Fri, 8 May 2009 14:42:58 -0700 (PDT)
Message-ID: <f55e67c8-0f3b-46aa-9477-b9b308769d80_at_e24g2000vbe.googlegroups.com>
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.
Date: Fri, 8 May 2009 14:42:58 -0700 (PDT)
Message-ID: <f55e67c8-0f3b-46aa-9477-b9b308769d80_at_e24g2000vbe.googlegroups.com>
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')))) Received on Fri May 08 2009 - 16:42:58 CDT