Re: DECODE

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 17 Apr 2010 22:13:43 +0200
Message-ID: <4BCA1677.90608_at_gmail.com>

On 17.04.2010 00:03, The Magnet wrote:
>
> Can you nest DECODE with different field criteria? Here is our
> DECODE:
>
> DECODE(SUM(DECODE(subproduct_id, 238, 1, 0)) OVER (PARTITION BY
> customer_id),0,'N','Y') ultimate_trader
>
> Now, with that, if the result is Y, meaning that they DO have this 238
> product, I want to see if they have a status = 'Active'. So, is there
> a way to implement yet another level, testing the status = 'Active' if
> the result of the above statement is 'Y'?
>
>
>
>

It is not clear for me, how looks your data and what is desired output. So, here is my interpretation of your business logic: If any particular record for a given customer_id has subproduct_id=238 and status='Active' - then all records for such customer should have ultimate_trader='Y', otherwise 'N'.
If it is correct interpretation, then just replace a positive branch of your decode with another decode (status test) - the same of course can be done with case:

SQL> with t as (

   2 select 1 customer_id,238 subproduct_id,'Active' status from dual union all

   3   select 1 ,238 ,'Passive' from dual union all
   4   select 1 ,238 ,'Ignorant' from dual union all
   5   select 1 ,238 ,'Excited' from dual union all
   6   select 2 ,238 ,'Active' from dual union all
   7   select 2 ,239 ,'Reactivated' from dual union all
   8   select 2 ,233 ,'Unhappy' from dual union all
   9   select 2 ,238 ,'Active' from dual union all
  10   select 3 ,231 ,'Passive' from dual union all
  11   select 3 ,238 ,'Ignorant' from dual union all
  12   select 3 ,238 ,'Excited' from dual union all
  13   select 3 ,232 ,'Active' from dual union all
  14   select 3 ,234 ,'Reactivated' from dual union all
  15   select 3 ,231 ,'Unhappy' from dual

  16 )
  17 select
  18 t.*,
  19 decode(sum(decode(subproduct_id,

  20                    238,
  21                    decode(status,'Active',1,0),
  22                    0)

  23 )
  24 over (partition by customer_id),0,'N','Y') ultimate_trader   25 from t
  26 /

  CUSTOMER_ID SUBPRODUCT_ID STATUS ULTIMATE_TRADER ------------ -------------- ------------ ---------------

            1            238 Passive      Y
            1            238 Active       Y
            1            238 Ignorant     Y
            1            238 Excited      Y
            2            239 Reactivated  Y
            2            238 Active       Y
            2            233 Unhappy      Y
            2            238 Active       Y
            3            231 Passive      N
            3            238 Ignorant     N
            3            238 Excited      N
            3            232 Active       N
            3            234 Reactivated  N
            3            231 Unhappy      N

14 rows selected.

Best regards

Maxim Received on Sat Apr 17 2010 - 15:13:43 CDT