Home » SQL & PL/SQL » SQL & PL/SQL » Decode function with AND & OR condition
Decode function with AND & OR condition [message #263772] Fri, 31 August 2007 00:26 Go to next message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi,

I'm here would like to seek help for computing decode function as below:

select * from tablePassport pp, tablePermit pm
(CASE WHEN pp.passport_expired is null THEN 'NPP' WHEN pm.permit_expired is null THEN 'NPM' WHEN pm.permit_expired+90> pp.passport_expired and pm.status ='Y' AND pm.permit_expired - sysdate < 90 then 'NVL' ELSE 'VL' END) = 'NVL'and pp.status is null);

We are using oracle 8.1.7, CASE is not supported, so have to go for the decode function.

Below is the statement tat I have tried..but fail...
\
DECODE(pp.passport_expired , null,'NPP', decode(pm.permit_expired , null,'NPM'), decode(pm.permit_expired+90> pp.passport_expired and pm.status ='Y' and pm.permit_expired - sysdate < 90, 'NVL','VL' )).

pls advise further........

ying

[Updated on: Fri, 31 August 2007 00:27]

Report message to a moderator

Re: Decode function with AND & OR condition [message #263776 is a reply to message #263772] Fri, 31 August 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
if you want to test if "a < b" with decode you have to test if "sign(a-b)=-1".

Regards
Michel
Re: Decode function with AND & OR condition [message #263778 is a reply to message #263772] Fri, 31 August 2007 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
decode( pp.passport_expired 
      , null, 'NPP'
      , decode( pm.permit_expired
              , null, 'NPM'
              , decode( sign(pm.permit_expired + 90 - pp.passport_expired)
                      , 1, decode( pm.status
                                 , 'Y', decode( sign(pm.permit_expired - 90 -  sysdate)
                                              , -1, 'NVL'
                                              , 'VL'
                                              )
                                 , 'VL'
                                 )
                      , 'VL'
                      )
              , 'VL'
              )
      , 'VL'
      )

Note: this is untested of course, since I have no data.
Further, I added all the 'VL' as default options; I think, especially some of the last 2 or 3 can be removed.
Re: Decode function with AND & OR condition [message #264237 is a reply to message #263778] Sun, 02 September 2007 05:12 Go to previous message
ying
Messages: 143
Registered: May 2002
Senior Member
Hi Frank,

Thanks so much...you really help me a lot, especially most of my doubt in decode function.

Again, thanks.

Ying
Previous Topic: how to copy tables from one user to another
Next Topic: Change into capital letters?
Goto Forum:
  


Current Time: Sat Dec 03 22:25:57 CST 2016

Total time taken to generate the page: 0.12948 seconds