Home » SQL & PL/SQL » SQL & PL/SQL » DECODE function
DECODE function [message #190076] Tue, 29 August 2006 04:11 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
How can I use the DECODE function to solve the following query:
if needdate in (sysdate,sysdate+1,sysdate+2,sysdate+3,sysdate+4,sysdate+5,sysdate+6,sysdate+7) then
pl.qty
else 0


Regards,
Mona
Re: DECODE function [message #190078 is a reply to message #190076] Tue, 29 August 2006 04:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Mona, take note that sysdate includes a time portion. That will influence the result.

With decode, you can only compare value pairs so a CASE construction might be easier to implement.

MHE
Re: DECODE function [message #190092 is a reply to message #190076] Tue, 29 August 2006 04:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or even simpler
if (needdate>= sysdate) and (needate < sysdate+8) then
...
Re: DECODE function [message #190097 is a reply to message #190092] Tue, 29 August 2006 04:53 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think this is part of a SELECT. "IF" could pose a problem in a select. Hence my CASE suggestion Wink

MHE
Re: DECODE function [message #190098 is a reply to message #190097] Tue, 29 August 2006 04:58 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks, I have used DECODE using your suggestion(if (needdate>= sysdate) and (needate < sysdate+8) then
)

DECODE(SIGN(v_date-pl.needdate),
       1,(DECODE(SIGN(v_date+6-pl.needdate,1,pl.qty,0,pl.qty,-1,0))),
       0,pl.qty,
       0)


Thanks again,
Mona
Re: DECODE function [message #190099 is a reply to message #190097] Tue, 29 August 2006 04:58 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
True, but the OP had an IF in it, so my suggestion will work no worse than that. If I can guarantee that things are no worse at the end of the day than they were at the start, it's cant have bee too bad a day Cool
Previous Topic: Hiding Package
Next Topic: Encryption problem while using Dbms_Crypto
Goto Forum:
  


Current Time: Sat Dec 03 22:18:28 CST 2016

Total time taken to generate the page: 0.04709 seconds