1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Case statement in pl/sql

Discussion in 'SQL PL/SQL' started by kamalalkarim, Jun 21, 2012.

  1. kamalalkarim

    kamalalkarim Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Dear All

    I would like to share a little problem facing in PL/Sql. I wan to run the following select statement in oracle forms 6i.

    SELECT TRUNC(SUM(CASE WHEN RLR_PROD_RATE>0 THEN (V_HCOST/RLR_PROD_RATE) ELSE 0 END),5) INTO V_ROUVAL
    FROM ROUTING_LN,ROUTING_LN_RATES
    WHERE RL_BUS_UNIT = 'JMTMP'
    AND RL_BUS_UNIT = RLR_BUS_UNIT
    AND RL_REF_NO = RLR_REF_NO
    AND ((RL_ITEM = V_ITEMBOM.BITEM));

    I put this statement in FOR LOOP. And when I compile the trigger, I got the following error message...

    Encountered the symbol "CASE" when expecting one of the following
    ( - + all mod null <an identifier>
    <a-double quoted delimited-identifier> <a bind variable> avg
    count current distinct max min prior sql stddev sum unique variance cast
    <a string literal with character set specification>
    <a number><a single quoted sql string>


    please help me to resolve the problem asap.

    best regards,

    Kamal
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    CASE works in PL/SQL in versions 10.2 and 11.x:

    Code (SQL):
    SQL> DECLARE
      2          cursor get_emp IS
      3          SELECT empno, hiredate, sal,
      4                  CASE WHEN comm IS NULL THEN 0 ELSE comm END comm
      5          FROM emp;
      6
      7  BEGIN
      8          FOR i IN get_emp loop
      9                  dbms_output.put_line(i.empno||' '||i.hiredate||'        '||i.sal||'     '||i.comm);
     10          END loop;
     11
     12  END;
     13  /
    7369    17-DEC-80       800     0
    7499    20-FEB-81       1600    300
    7521    22-FEB-81       1250    500
    7566    02-APR-81       2975    0
    7654    28-SEP-81       1250    1400
    7698    01-MAY-81       2850    0
    7782    09-JUN-81       2450    0
    7788    09-DEC-82       3000    0
    7839    17-NOV-81       5000    0
    7844    08-SEP-81       1500    0
    7876    12-JAN-83       1100    0
    7900    03-DEC-81       950     0
    7902    03-DEC-81       3000    0
    7934    23-JAN-82       1300    0
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    It's not PL/SQL that's the problem, it's Forms 6i, which doesn't support CASE. You could possibly use DECODE:

    Code (SQL):
    SQL> DECLARE
      2          cursor get_emp IS
      3          SELECT empno, hiredate, sal,
      4                 decode(comm, NULL, 0, comm) comm
      5          FROM emp;
      6
      7  BEGIN
      8          FOR i IN get_emp loop
      9                  dbms_output.put_line(i.empno||' '||i.hiredate||'        '||i.sal||'     '||i.comm);
     10          END loop;
     11
     12  END;
     13  /
    7369    17-DEC-80       800     0
    7499    20-FEB-81       1600    300
    7521    22-FEB-81       1250    500
    7566    02-APR-81       2975    0
    7654    28-SEP-81       1250    1400
    7698    01-MAY-81       2850    0
    7782    09-JUN-81       2450    0
    7788    09-DEC-82       3000    0
    7839    17-NOV-81       5000    0
    7844    08-SEP-81       1500    0
    7876    12-JAN-83       1100    0
    7900    03-DEC-81       950     0
    7902    03-DEC-81       3000    0
    7934    23-JAN-82       1300    0
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
     
  3. kamalalkarim

    kamalalkarim Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi David

    Thank you for your reply. It is clear now but only problem i want to check the condition by using logical operator like ( < , > etc). So Decode can help me in this regard.

    thanks in advance.
    best regards,

    Kamal
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi ,

    Code (Text):
    DECLARE
      2          cursor get_emp IS
      3 SELECT empno, hiredate, sal,
      4 DECODE(comm, null,0,comm)comm
      5 FROM emp;
      6
      7 BEGIN
      8 FOR i IN get_emp loop
      9                  dbms_output.put_line(i.empno||' '||i.hiredate||'        '||i.sal||'     '||i.comm);
     10 END loop;
     11
     12 END;
     13 /
    7369 17-DEC-80 800 0
    7499 20-FEB-81 1600 300
    7521 22-FEB-81 1250 500
    7566    02-APR-81 2975 0
    7654 28-SEP-81 1250 1400
    7698    01-MAY-81 2850 0
    7782    09-JUN-81 2450 0
    7788    09-DEC-82 3000 0
    7839 17-NOV-81 5000 0
    7844    08-SEP-81 1500 0
    7876 12-JAN-83 1100 0
    7900    03-DEC-81 950 0
    7902    03-DEC-81 3000 0
    7934 23-JAN-82 1300 0
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    This was exactly looks like david's example but I have just changed logic into decode statement.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    it is my example as I also uwed decode in the second code sample. Apparently you did not see the second sql statement using decode in my post.