OraFAQ Forum: SQL & PL/SQL » Swicth Case Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Swicth Case Statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Swicth Case Statement [message #9330]

Wed, 05 November 2003 02:03

Ferdeen Mughal
Messages: 2
Registered: November 2003

Junior Member

Hello,

I'm trying to find a way of using the switch case statement for determing if a value is either "A", "B"...through to "H".

I could do the following :-

switch ([[VALUE]])
{
case 'A','B'...,'H':
[[DO SOMETHING]];
break;
case 'X':
[[DO SOMETHING ELSE]];
break;
}

Is there a better way of doing this ?

Thanks,
Ferdeen

Report message to a moderator

Re: Swicth Case Statement [message #9331 is a reply to message #9330]

Wed, 05 November 2003 02:23

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

In SQL, PL/SQL, or the language your example was in?

Report message to a moderator

Re: Swicth Case Statement [message #9334 is a reply to message #9331]

Wed, 05 November 2003 02:45

ferdeen.mughal@rbs.co.uk
Messages: 1
Registered: November 2003

Junior Member

sorry PL/SQL

Report message to a moderator

Re: Swicth Case Statement [message #9335 is a reply to message #9331]

Wed, 05 November 2003 02:47

Reema
Messages: 50
Registered: July 2003

Member

Hi,
If you want a pl/SQL code for this you can use something like this
*******************************************
DECLARE
Cursor c1 is select ch from alphabet;
BEGIN
for ALPHA IN C1 LOOP
If alpha.ch = 'A' OR alpha.ch = 'B' OR alpha.ch = 'C' OR ........THEN
dBMS_OUTPUT.PUT_LINE('you ron right track');
else
dBMS_OUTPUT.PUT_LINE('you AGAIN DUMPED');
END IF;
eND lOOP;
end;
/
****************************************
and in SQL it could be attempted somethign like this
***************************************
sELECT CASE WHEN CH BETWEEN 'A' AND 'H' THEN CH ELSE '' END FROM ALPHABET;
I Hope it helps ..In case of any clarification you can revert. Any improvement solutions are more then welcome..

Report message to a moderator

Re: Switch Case Statement [message #9338 is a reply to message #9335]

Wed, 05 November 2003 03:59

Ferdeen
Messages: 1
Registered: November 2003

Junior Member

Reema,

Thanks, I like the SQL solution (using BETWEEN), is there no equivalent in pl/SQL ?

Thanks,
Ferdeen

Report message to a moderator

Re: Swicth Case Statement [message #9339 is a reply to message #9330]

Wed, 05 November 2003 04:06

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You mean like this:

SQL> Declare
  2    v_alpha Varchar2(1) := '&input';
  3  Begin
  4    If Upper(v_alpha) Between 'A' And 'H
  5    Then
  6      dbms_output.put_line('First switch');
  7    Elsif Upper(v_alpha) = 'X'
  8    Then
  9      dbms_output.put_line('Second switch');
 10    Else
 11      dbms_output.put_line('Else');
 12    End if;
 13  End;
 14  /
Enter value for input: A
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'A';
First switch

PL/SQL procedure successfully completed.

SQL> /
Enter value for input: B
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'B';
First switch

PL/SQL procedure successfully completed.

SQL> /
Enter value for input: d
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'd';
First switch

PL/SQL procedure successfully completed.

SQL> /
Enter value for input: x
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'x';
Second switch

PL/SQL procedure successfully completed.

SQL> /
Enter value for input: h
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'h';
First switch

PL/SQL procedure successfully completed.

SQL> /
Enter value for input: j
old   2:   v_alpha Varchar2(1) := '&input';
new   2:   v_alpha Varchar2(1) := 'j';
Else

PL/SQL procedure successfully completed.

SQL>

MHE

Report message to a moderator

Re: Swicth Case Statement [message #9342 is a reply to message #9339]

Wed, 05 November 2003 04:15

Ferdeen Mughal
Messages: 2
Registered: November 2003

Junior Member

Thanks, I didn't realise the "BETWEEN" function caters for ASCII Character sets. I will go with this solution.

Report message to a moderator

Re: Switch Case Statement [message #9343 is a reply to message #9338]

Wed, 05 November 2003 04:28

Reema
Messages: 50
Registered: July 2003

Member

Oh.. m sorry for missing that very true....U have a exact equivalent in PL/SQL....Instead of using
If <Var> = 'A' or 'B' or 'C' ... you can usee
IF <var> Between 'A' and 'H' then
******************
It should work.

Report message to a moderator

Re: Switch Case Statement [message #9353 is a reply to message #9343]

Wed, 05 November 2003 16:51

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

Yes PL/SQL has a CASE statement, in a couple of flavours:<pre style="color: navy; font-size: 9pt;">
DECLARE
v_testval VARCHAR2(1) := 'B';
BEGIN
IF v_testval BETWEEN 'A' AND 'C' THEN
DBMS_OUTPUT.PUT_LINE(v_testval || ' is between A and C');
END IF;

CASE v_testval
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE(v_testval || ' = A (simple CASE statement)');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE(v_testval || ' = B (simple CASE statement)');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE(v_testval || ' = C (simple CASE statement)');
ELSE DBMS_OUTPUT.PUT_LINE(v_testval || ' unknown (simple CASE statement)');
END CASE;

CASE
WHEN v_testval BETWEEN 'A' AND 'C' THEN
DBMS_OUTPUT.PUT_LINE(v_testval || ' is between A and C (searched CASE statement)');
WHEN v_testval IN ('A','B','C') THEN
DBMS_OUTPUT.PUT_LINE(v_testval || ' is in list (A, B, C) (searched CASE statement)');
ELSE DBMS_OUTPUT.PUT_LINE(v_testval || ' unknown (searched CASE statement)');
END CASE;

DBMS_OUTPUT.PUT_LINE(
CASE
WHEN v_testval IN ('A','B','C') THEN 'This is a nested simple CASE expression'
ELSE NULL
END );
END;
/</pre>

Report message to a moderator

Re: Switch Case Statement [message #10566 is a reply to message #9353]

Fri, 30 January 2004 12:51

ishita
Messages: 15
Registered: April 2003

Junior Member

Does the above "CASE" work in Oracle8i?

I was unable to run the same in Oracle 8.1.7.2.0

Please let me know if it is a added feature of 9i alone
?

Thanks,
Ishita

Report message to a moderator

Re: Switch Case Statement [message #10567 is a reply to message #10566]

Fri, 30 January 2004 14:57

William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK

Senior Member

CASE appeared in SQL in 8i, and finally made it into PL/SQL in 9i.

Report message to a moderator

Previous Topic:	conditional logic
Next Topic:	Need a function which will tell number of ',' in string

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 08:02:05 CDT 2024