Home » SQL & PL/SQL » SQL & PL/SQL » Case Expression (9i)
Case Expression [message #359739] Tue, 18 November 2008 02:06 Go to next message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
I read in Scott Urman that we can't use decode function in procedural statement but we can use case expression.

DECLARE
A NUMBER:=9;
BEGIN
IF (CASE A WHEN 1 THEN 10
WHEN 2 THEN 20
WHEN 9 THEN 90
ELSE 100
END) THEN
DBMS_OUTPUT.PUT_LINE(A);
END IF;
END;

I done this but it is showing error.
Will someone help me.
Re: Case Expression [message #359744 is a reply to message #359739] Tue, 18 November 2008 02:11 Go to previous messageGo to next message
tarmenel@gmail.com
Messages: 18
Registered: November 2008
Junior Member
You are using an if statement so would need a test expression. It needs to return TRUE or FALSE.
((CASE a WHEN 1 THEN 10 WHEN 2 THEN 20 WHEN 9 THEN 90 ELSE 100 END) = 90)
Re: Case Expression [message #359747 is a reply to message #359744] Tue, 18 November 2008 02:20 Go to previous messageGo to next message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Thanks my friend. I got your answer.
But suppose i want to make if 1 then the value of a is 10 and
if a is 9 then a value convert to 90 using case expression then what i want to do.
Re: Case Expression [message #359749 is a reply to message #359747] Tue, 18 November 2008 02:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A := CASE ...

Regards
Michel
Re: Case Expression [message #359750 is a reply to message #359747] Tue, 18 November 2008 02:24 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In that case, you don't need IF-THEN-ELSE construct; simply do the following:
DECLARE
   a  NUMBER := 9;
BEGIN
  a := CASE a 
          WHEN 1
             THEN 10
          WHEN 2
             THEN 20
          WHEN 9
             THEN 90
          ELSE 100
       END;
  DBMS_OUTPUT.put_line (a);
END;
Re: Case Expression [message #359775 is a reply to message #359750] Tue, 18 November 2008 03:05 Go to previous message
das.liton
Messages: 9
Registered: November 2008
Location: Bangalore
Junior Member
Thanks both of you. Michael and Littlefoot.
Previous Topic: Sql Query
Next Topic: Swapping Synonyms in production
Goto Forum:
  


Current Time: Sat Dec 10 20:17:47 CST 2016

Total time taken to generate the page: 0.10398 seconds