Re: Failing Query

From: The Magnet <art_at_unsu.com>
Date: Wed, 13 Apr 2011 13:06:41 -0700 (PDT)
Message-ID: <2e4ab47d-4e9b-443d-9009-231bee5b7a4c_at_cu4g2000vbb.googlegroups.com>

On Apr 13, 2:39 pm, Lennart Jonsson <erik.lennart.jons..._at_gmail.com> wrote:
> On 04/13/2011 07:54 PM, The Magnet wrote:
> [...]
>
> > Lennart,
>
> > Maybe I am not understanding, but those subqueries are really
> > 'columns'.  So, I should be able to return a NULL for a column value,
> > yes?
>
> No they are not, they are tables in your query. Because one of those
> tables are empty, the result is empty. Let's rewrite the query as:
>
> WITH T2 AS (
>     SELECT trial_status
>     FROM (
>         SELECT order_date, co.status trial_status,
>                ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
>         FROM customer_order_vw co, newsletter_subscription ns
>         WHERE customer_id = 931044855
>           AND subproduct_id = 197
>           AND co.code = ns.code
>           AND subscr_type = 'Trial'
>     ) AS T1
>     WHERE rnum = 1
> ), T4 AS (
>     SELECT paid_status
>     FROM (
>         SELECT order_date, 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'
>     ) AS T3
>     WHERE rnum = 1
> )
> SELECT T2.trial_status, T4.paid_status
> FROM T2, T4;
>
> Do you agree that if T4 is empty the result is empty?
>
> I don't have an Oracle installation at hand to try with, but I guess
> something like below should do:
>
> WITH T AS (
>     SELECT co.status
>          , CASE ns.subscr_type
>            WHEN 'Trial' then 'trial_status'
>            ELSE 'paid_status'
>            END as status_type
>          , ROW_NUMBER() OVER (
>                 PARTITION BY CASE ns.subscr_type
>                              WHEN 'Trial' then 'trial_status'
>                              ELSE 'paid_status'
>                              END                
>                 ORDER BY order_date DESC
>           ) as rnum
>         FROM customer_order_vw co
>         JOIN newsletter_subscription ns
>             ON co.code = ns.code
>         WHERE customer_id = 931044855
>           AND subproduct_id = 197
> )
> SELECT status_type, status
> FROM T
> WHERE rnum = 1
>
> or if you insist on pivoting:
>
> WITH T AS (
>     SELECT co.status
>          , CASE ns.subscr_type
>            WHEN 'Trial' then 'trial_status'
>            ELSE 'paid_status'
>            END as status_type
>          , ROW_NUMBER() OVER (
>                 PARTITION BY CASE ns.subscr_type
>                              WHEN 'Trial' then 'trial_status'
>                              ELSE 'paid_status'
>                              END                
>                 ORDER BY order_date DESC
>           ) as rnum
>         FROM customer_order_vw co
>         JOIN newsletter_subscription ns
>             ON co.code = ns.code
>         WHERE customer_id = 931044855
>           AND subproduct_id = 197
> )
> SELECT
>     (SELECT status FROM T
>      WHERE rnum = 1
>        AND status_type = 'trial_status') as trial_status
>    ,(SELECT status FROM T
>      WHERE rnum = 1
>        AND status_type = 'paid_status') as paid_status
> FROM DUAL
>
> /Lennart

Lennart,

You the man. Thank you so very much!!!

That is a good and somewhat complex query. Very nice. Thank you! Received on Wed Apr 13 2011 - 15:06:41 CDT