Re: Failing Query

From: The Magnet <art_at_unsu.com>
Date: Thu, 14 Apr 2011 06:40:48 -0700 (PDT)
Message-ID: <ae28ceee-5d2b-434b-b209-88d48746a136_at_z7g2000prh.googlegroups.com>

On Apr 13, 6:13 pm, Peter Nilsson <ai..._at_acay.com.au> wrote:
> Lennart Jonsson <erik.lennart.jons..._at_gmail.com> wrote:
> > 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
>
> max() keeps are generally more efficient than row_number() = 1
>
>   select max(decode(subscr_type, 'Trial', co.status, null)) keep
>            (dense_rank last order by
>               decode(subscr_type, 'Trial', 1, 0),
>               co.order_date)
>            as trial_status,
>          max(decode(subscr_type, 'Trial', null, co.status)) keep
>            (dense_rank last order by
>               decode(subscr_type, 'Trial', 0, 1),
>               co.order_date)
>            as paid_status
>     from customer_order_vw co
>     join newsletter_subscription ns on ns.code = co.code
>    where co.customer_id = 732126295
>      and subproduct_id = 197
>
> --
> Peter

Peter,

This is also a good solution. Many thanks! Received on Thu Apr 14 2011 - 08:40:48 CDT