| decode [message #39275] |
Tue, 02 July 2002 17:57  |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
|
Can anyone tell me about the usage of 'DECODE' ??
|
|
|
|
| Re: decode [message #39276 is a reply to message #39275] |
Tue, 02 July 2002 18:39  |
wangsanjun
Messages: 13
Registered: June 2002
|
Junior Member |
|
|
DECODE(condition,v1,r1,v2,r2,...vn,rn,default) means as the following:
if condition=v1 then
return(r1);
elsif condition=v2 then
return(r2);
elsif condition=v3 then
return(r3);
.....
else
return(default);
end if;
|
|
|
|
| Re: decode [message #39278 is a reply to message #39275] |
Tue, 02 July 2002 19:11 |
Rozario Mariadassou
Messages: 12
Registered: March 2002
|
Junior Member |
|
|
Decode is a very powerful function. For eg, if I want a cross tabulated output with yrwise sales in each coln for the last 3 yrs, the sql can be written as
select unit_code, sum(decode(to_char(trunc(docdate,'yy'),'yyyy'),'2000',fxnetamt,0)) order_00,
sum(decode(to_char(trunc(docdate,'yy'),'yyyy'),'2001',fxnetamt,0)) order_01,
sum(decode(to_char(trunc(docdate,'yy'),'yyyy'),'2002',fxnetamt,0)) order_02
from dohead group by unit_code
What this does is if (to_char(trunc(docdate,'yy'),'yyyy') = '2000', then it will add order_qty otherwise it will add 0.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
