Home » SQL & PL/SQL » SQL & PL/SQL » CASE/DECODE (oracle9.2.0.3)
CASE/DECODE [message #424871] Tue, 06 October 2009 06:15 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I want to implement the below condition using CASE/DECODE...


IF

CURR_STATS is not null or CURR_STATS!=" " THEN

STATUS=IN

ELSIF

If m_out_param = "RTS" And DELETE_IND > 0 Then

then status =iDEL

else status=DEL

END;


I tried this way but cant make out the right way of coding for this. Please help!

select
..

CASE WHEN (nvl(rtrim(CURR_STATS),0)=1) then 'IN' --if CURR_STATS is not null 
                            WHEN ((nvl(rtrim(CURR_STATS),0)=0) and m_out_param='RTS' AND (select count(*) from item i where i.status_cde = 'I' and i.item_id = n.item_id) > 0) THEN 'iDEL'
                                      ELSE 'DEL' END as "Status"
from
nitems n,
code c
where
...
 

Re: CASE/DECODE [message #424874 is a reply to message #424871] Tue, 06 October 2009 06:38 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
So what is the error you are facing?

regards,
Delna
Re: CASE/DECODE [message #424875 is a reply to message #424871] Tue, 06 October 2009 06:38 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
IF

CURR_STATS is not null or CURR_STATS!=" " THEN

STATUS=IN

ELSIF

If m_out_param = "RTS" And DELETE_IND > 0 Then

then status =iDEL

else status=DEL

END;


Using Case I tried this way but still unable to figure out how to make the else part work as per the pseudo logic above

CASE WHEN (nvl(rtrim(CURR_STATS),0)=1) then 'IN' --if CURR_STATS is not null 
                            WHEN ((nvl(rtrim(CURR_STATS),0)=0) and m_out_param='RTS' AND (DELETE_IND > 0) THEN 'iDEL'
                                      ELSE 'DEL' END as "Status"
Re: CASE/DECODE [message #424876 is a reply to message #424874] Tue, 06 October 2009 06:42 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
The code written as per the logic is not correct. How to modifiy
the above pseudo logic using CASE

Thanks
Re: CASE/DECODE [message #424878 is a reply to message #424875] Tue, 06 October 2009 06:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This
nvl(rtrim(CURR_STATS),0)=1
is not the same as saying that Curr_stats is not null and not equal to spaces. If CURR_STATS = 3 then it won't get picked up by this clause.

You'd want
WHEN rtrim(CURR_STATS) IS NOT NULL
for that

Your second WHEN doesn't need this bit:
(nvl(rtrim(CURR_STATS),0)=0)
as that will pick CURR_STATS lines that are null, or spaces or 0
Re: CASE/DECODE [message #424880 is a reply to message #424875] Tue, 06 October 2009 06:47 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I don't understand your requirement and logic you applied to achieve that, but syntactically it is wrong.
One ending round bracket ')' is missing.
So it should not be executed, and hence you can't say it logically malfunctioning.
And if it is working perfactly, then please show some output with test data.

regards,
Delna
Re: CASE/DECODE [message #424884 is a reply to message #424878] Tue, 06 October 2009 07:03 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks Sir for pointing out the mistake I have made.


I modified this way..

CASE  WHEN rtrim(CURR_STATS) IS NOT NULL then 'IN' --if CURR_STATS is not null 
                            WHEN m_out_param='RTS' AND (DELETE_IND > 0) THEN 'iDEL'
                                      ELSE 'DEL' END as "Status"




Re: CASE/DECODE [message #424885 is a reply to message #424884] Tue, 06 October 2009 07:09 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, I have a concern does the second condition will
pick CURR_STATS lines that are null, or spaces ??
Re: CASE/DECODE [message #424888 is a reply to message #424885] Tue, 06 October 2009 07:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes - if CURR_STATS is null or spaces, then the CASE statement will check to see if the second condition is met.
Re: CASE/DECODE [message #424889 is a reply to message #424888] Tue, 06 October 2009 07:26 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thank you very much ! And the Else part will take care of if its null then status would be 'DEL'
Re: CASE/DECODE [message #424897 is a reply to message #424889] Tue, 06 October 2009 07:50 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will get a status of 'DEL' if CURR_STATS is spaces or null, and if either (m_out_param is null or not equal to 'RTS') or (DELETE_IND is null or <= 0)
Previous Topic: Creating Indexes in Views
Next Topic: Gregorian Date to Islamic Hijri Date and Hijri Date to Gregorian Date in Oracle using pl/sql
Goto Forum:
  


Current Time: Thu Sep 29 14:15:58 CDT 2016

Total time taken to generate the page: 0.08956 seconds