This discussion is archived
5 Replies Latest reply: Dec 11, 2012 3:23 AM by APC RSS

more than one variable at a time in case statement

750281 Newbie
Currently Being Moderated
hi i am using oracle 10g database.

can it be possible ?
     in if condition we can have more than one variable in one condition 
       if a=100 then
           b:=200;
           c:=300;
       end if;
 
how it will be in case statement ? i have searched lot on internet but didnt get

case
when a=100 then 200, 300 ... ???



thanks
  • 1. Re: more than one variable at a time in case statement
    Vivek L Expert
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    hi i am using oracle 10g database.

    can it be possible ?
    in if condition we can have more than one variable in one condition 
    if a=100 then
    b:=200;
    c:=300;
    end if;
    how it will be in case statement ? i have searched lot on internet but didnt get

    case
    when a=100 then 200, 300 ... ???



    thanks
    not sure if I got your question correctly, is this what you are looking for?
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2      a number := 100;
      3      b number;
      4      c number;
      5  begin
      6      case
      7        when a = 100 then
      8          b := 200;
      9          c := 300;
     10        when a = 0 then
     11          b := 300;
     12          c := 400;
     13        else
     14          b := 0;
     15          c := 0;
     16      end case;
     17  dbms_output.put_line('a :'||a||' b :'||b||' c :'||c);
     18* end;
    SQL> /
    a :100 b :200 c :300
    
    PL/SQL procedure successfully completed.
  • 2. Re: more than one variable at a time in case statement
    BluShadow Guru Moderator
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    hi i am using oracle 10g database.

    can it be possible ?
    in if condition we can have more than one variable in one condition 
    if a=100 then
    b:=200;
    c:=300;
    end if;
    how it will be in case statement ? i have searched lot on internet but didnt get

    case
    when a=100 then 200, 300 ... ???



    thanks
    If the case statement is part of an SQL projection (column returned in a select statement) or is part of an assignment e.g. variable := case ... then you can only return/assign a single value.

    If you are using the case statement as a standalone statement in PL/SQL code then you can use it the same as the IF statement..
    CASE WHEN a = 100 THEN
            b:=200;
            c:=300;
         WHEN a = 150 THEN
            b:=250;
            c:=350;
    ELSE
      b:=100;
      c:=150;
    END CASE;
    So what are you actually trying to achieve?
  • 3. Re: more than one variable at a time in case statement
    APC Oracle ACE
    Currently Being Moderated
    You can't do that with a simple CASE statement, only searched CASE.
    CASE
      WHEN  a=100 THEN
               b:=200;
               c:=300;
    END CASE;
    Put like that it seems a little pointless, but it's your example not mone ;)

    Of course, this only works in PL/SQL. When using CASE() in a SQL query each branch can only retrun one value, whether using the simple or searched syntax. That limitation is impled in [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm#sthref1848]the SQL Reference but is not explicitly stated.

    Cheers, APC
  • 4. Re: more than one variable at a time in case statement
    750281 Newbie
    Currently Being Moderated
    thanks.

    i want to use case in pl/sql instead of if and end if statement;
  • 5. Re: more than one variable at a time in case statement
    APC Oracle ACE
    Currently Being Moderated
    Hi FRNzzz!! wrote:
    i want to use case in pl/sql instead of if and end if statement;
    The Oracle documentation is comprehensive, online and free. Please learn to use it instead of posting trivial syntax questions in these forums. For instance, [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/case_statement.htm#sthref2513]here is the CASE syntax section ofthe PL/SQL Ref.

    Cheers, APC

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points