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.

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