Subquery

From: The Magnet <art_at_unsu.com>
Date: Tue, 12 Apr 2011 18:52:25 -0700 (PDT)
Message-ID: <270fba94-cb10-4411-8ea4-07795f541001_at_dn9g2000vbb.googlegroups.com>

Hi, I have this query / subquery that is acting strange. I am trying to get the latest status of a customers Trial subscription and Paid subscription. The customer may have 1, both or neither.

But, in the case where the customer has 1, say a Trial and not a Paid, the entire query fails and returns nothing. Any thoughts?

SELECT trial_status, paid_status
FROM (SELECT trial_status

           FROM (SELECT co.status trial_status, ROW_NUMBER() OVER
(ORDER BY order_date DESC) rnum

                      FROM customer_order_vw co,
newsletter_subscription ns
                      WHERE customer_id = 732126295
                         AND subproduct_id = 197
                         AND co.code = ns.code
                         AND subscr_type = 'Trial')
            WHERE rnum = 1),
           (SELECT paid_status
            FROM (SELECT co.status paid_status, ROW_NUMBER() OVER


(ORDER BY order_date DESC) rnum

                        FROM customer_order_vw co,
newsletter_subscription ns
                        WHERE customer_id = 732126295
                             AND subproduct_id = 197
                             AND co.code = ns.code
                             AND subscr_type <> 'Trial')
            WHERE rnum = 1);

Received on Tue Apr 12 2011 - 20:52:25 CDT