Re: Failing Query

From: Peter Nilsson <airia_at_acay.com.au>
Date: Wed, 13 Apr 2011 16:13:31 -0700 (PDT)
Message-ID: <4c3f285a-1b54-4836-bd2b-0e821374c44a_at_w9g2000prg.googlegroups.com>

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

Received on Wed Apr 13 2011 - 18:13:31 CDT