Re: Subquery

From: Donatello Settembrino <donatello.settembrino_at_gmail.com>
Date: Tue, 19 Apr 2011 04:15:41 -0700 (PDT)
Message-ID: <9a78f3d8-bd6f-4cda-90b8-6f8e75ac3a37_at_r19g2000prm.googlegroups.com>

On 13 Apr, 03:52, 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);

There are some details to point out

1. because it uses a Cartesian product?
2. How many values(distinct) contains the column subscr_type?

Please provide an example with data.
what are the expected results?

However, in order to give an answer (although I'm not sure I understood the problem) try using the following query:

SELECT max(trial_status), max(paid_status) FROM (SELECT trial_status, null as paid_status

      FROM (SELECT co.status trial_status
              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'
             order by order_date desc)
      WHERE rownum = 1
      union all
      SELECT null, paid_status
      FROM (SELECT co.status paid_status
             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'
             order by order_date desc  )
       WHERE rnum = 1) ;

HTH Donatello Settembrino Received on Tue Apr 19 2011 - 06:15:41 CDT