5 Replies Latest reply: Dec 11, 2012 5:23 AM by APC RSS

    more than one variable at a time in case statement

    750281
      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
          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
            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
              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
                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
                  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