Re: Subquery

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 13 Apr 2011 07:50:11 -0700 (PDT)
Message-ID: <66e182cd-be7f-448d-aaf5-bf55b333c4b6_at_l14g2000pre.googlegroups.com>

On Apr 12, 9:52 pm, The Magnet <a..._at_unsu.com> wrote:
> 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);

Some create table DDL and a few inserts might allow someone to set up the example and produce the desired result set.

HTH -- Mark D Powell -- Received on Wed Apr 13 2011 - 09:50:11 CDT