DECODE
in [Oracle]
|
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? |