Home » SQL & PL/SQL » SQL & PL/SQL » Need equivalent DECODE for CASE statement (Oracle8i Enterprise Edition Release 8.1.7.2.0)
Need equivalent DECODE for CASE statement [message #577883] Thu, 21 February 2013 08:27 Go to next message
apricot
Messages: 5
Registered: February 2013
Location: Glasgow
Junior Member
case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age

the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode

Re: Need equivalent DECODE for CASE statement [message #577885 is a reply to message #577883] Thu, 21 February 2013 08:30 Go to previous messageGo to next message
BlackSwan
Messages: 22677
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Need equivalent DECODE for CASE statement [message #577886 is a reply to message #577885] Thu, 21 February 2013 08:39 Go to previous messageGo to next message
apricot
Messages: 5
Registered: February 2013
Location: Glasgow
Junior Member
I did search before posting but didn't find anything useful.

Thanks.
Re: Need equivalent DECODE for CASE statement [message #577889 is a reply to message #577886] Thu, 21 February 2013 09:00 Go to previous messageGo to next message
cookiemonster
Messages: 10896
Registered: September 2008
Location: Rainy Manchester
Senior Member
One approach would be to use some simple arithmetic and the sign function to get a consistent value for ages less than 18 and ages greater than 39.
Then you'll need a nested decode to cater for low and high ranges. See if you can work it out from this:
SQL> WITH DATA AS (SELECT ROWNUM + 10 age FROM dual CONNECT BY LEVEL < 15)
SELECT age,
sign(age -18)
FROM DATA;  2    3    4

       AGE SIGN(AGE-18)
---------- ------------
        11           -1
        12           -1
        13           -1
        14           -1
        15           -1
        16           -1
        17           -1
        18            0
        19            1
        20            1
        21            1

       AGE SIGN(AGE-18)
---------- ------------
        22            1
        23            1
        24            1

14 rows selected.
Re: Need equivalent DECODE for CASE statement [message #577895 is a reply to message #577889] Thu, 21 February 2013 09:31 Go to previous messageGo to next message
Bill B
Messages: 1081
Registered: December 2004
Senior Member
decode(greatest(age,17),17,'<= 17',decode(least(age,40),40,'>= 40',to_char(age)))
Re: Need equivalent DECODE for CASE statement [message #577896 is a reply to message #577889] Thu, 21 February 2013 09:35 Go to previous messageGo to next message
apricot
Messages: 5
Registered: February 2013
Location: Glasgow
Junior Member
I think this may provide what I'm looking for - thanks cookiemonster

select ano_id,
       age,
       decode(a,0,'<=17',-1,'<=17',1,decode(b,0,'>=40',1,'>=40',age))
from (
      select t.ano_id,
             t.age,
             sign(t.age-17) a,
             sign(t.age-40) b
      from table t
--      where t.age > 17 and t.age < 40
     )
Re: Need equivalent DECODE for CASE statement [message #577897 is a reply to message #577895] Thu, 21 February 2013 09:45 Go to previous messageGo to next message
apricot
Messages: 5
Registered: February 2013
Location: Glasgow
Junior Member
Bill B wrote on Thu, 21 February 2013 09:31
decode(greatest(age,17),17,'<= 17',decode(least(age,40),40,'>= 40',to_char(age)))


that appears to work as well and is much simpler Smile
Re: Need equivalent DECODE for CASE statement [message #577901 is a reply to message #577897] Thu, 21 February 2013 10:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And why don't you want to use CASE?

Regards
Michel
Re: Need equivalent DECODE for CASE statement [message #577903 is a reply to message #577901] Thu, 21 February 2013 10:15 Go to previous messageGo to next message
apricot
Messages: 5
Registered: February 2013
Location: Glasgow
Junior Member
Michel Cadot wrote on Thu, 21 February 2013 10:13
And why don't you want to use CASE?

Regards
Michel


I need to use the code on Oracle 8i within a cursor and the case statement is not supported.
Re: Need equivalent DECODE for CASE statement [message #577904 is a reply to message #577901] Thu, 21 February 2013 10:15 Go to previous messageGo to next message
Bill B
Messages: 1081
Registered: December 2004
Senior Member
Michel Cadot wrote on Thu, 21 February 2013 16:13
And why don't you want to use CASE?

Regards
Michel

Unfortunally in 8.1.7.4 the sql engine will not allow a case statement in a select in a pl/sql block but will allow it in a plain sql select. Real PITA.

[Updated on: Thu, 21 February 2013 10:18]

Report message to a moderator

Re: Need equivalent DECODE for CASE statement [message #577906 is a reply to message #577904] Thu, 21 February 2013 10:22 Go to previous message
Michel Cadot
Messages: 58833
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Gee! Didn't see the version in the title (and in post, quite blindy today). Thanks for pointing me to it.

Regards
Michel

[Updated on: Thu, 21 February 2013 10:23]

Report message to a moderator

Previous Topic: same sequence number for repeating data
Next Topic: Count data
Goto Forum:
  


Current Time: Wed Aug 20 02:21:30 CDT 2014

Total time taken to generate the page: 0.12186 seconds