Prev: 11gR2 Or 10gR2 on Linux x64?
Next: What does the COST(%CPU) in explain plan mean?
From: Neil.W.James on 17 Apr 2010 09:54
On 17/04/2010 07:36, The Magnet wrote:
> On Apr 16, 5:33 pm, John Hurley <hurleyjo...(a)yahoo.com> wrote:
>> On Apr 16, 6:03 pm, The Magnet <a...(a)unsu.com> wrote:
>>
>> snip
>>
>>> 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'?
>>
>> Did you try testing what you wanted to do?
>>
>> Have you ever heard of inline views?
>
> Oh yeah, the hard part about this whole thing is that I need to
> PARTITION it by customer ID. So, within each partition I need to know
> if the customer has product 238 and if he does if the status is
> Active.
>
> All in one select. How fun.

The nested case suggested earlier sounds good.

But looking at it another way, aren't you wanting to know whether the
customer has a subproduct 238 or not. Sounds more like an existence test
- particularly since you're not interested in how many matches there are.

Regards,
Neil
From: Maxim Demenko on 17 Apr 2010 16:13
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
First  |  Prev  | 
Pages: 1 2
Prev: 11gR2 Or 10gR2 on Linux x64?
Next: What does the COST(%CPU) in explain plan mean?