pl/sql if loop not working

Question

I have to alter the sal but it is choosing the wrong else statement when running the PL/SQL

Here is my newemp3 table:

SQL> select *
  2  from newemp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

     7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

here is my code:

SET VERIFY OFF
DECLARE
   v_ename    newemp3.ename%TYPE; 
   v_sal      newemp3.sal%TYPE;
   v_newgoal  newemp3.sal%TYPE;
   v_deptno   newemp3.deptno%TYPE;
BEGIN
   --assign some value
   v_ename := null; --if null - it updates all records in table

   for rc in (SELECT ename, sal, deptno
     FROM newemp3
     WHERE ename = nvl(v_ename, ename)) loop

     IF rc.deptno = 10 AND rc.sal > 2000 then 
       v_newgoal := rc.sal * 1.06;
     else 
       v_newgoal := rc.sal * 1.07; 

       IF rc.deptno = 20 AND rc.sal > 2500 then 
         v_newgoal := rc.sal * 1.05;
       else 
         v_newgoal := rc.sal * 1.055;

         IF rc.deptno = 30 AND rc.sal > 1000 then 
           v_newgoal := rc.sal * 1.07;
         else 
           v_newgoal := rc.sal * 1.065;
         END IF;

       END IF; 

     END IF; 

     UPDATE newemp3
     SET sal = v_newgoal
     WHERE ename = rc.ename;

   end loop;

COMMIT;
END;
/
SET VERIFY ON

The last row is miller which has a 1300 sal. So that should be 1300 * 1.07 which equals 1391. The problem is, it is coming up with 1384.5, which is using the 1.065 from the dept 30 if statement.

Answer 1

Let's follow the logic for the row you're interested in for Miller, who has a salary of 1300 and a DEPTNO of 10:

1. Is Miller's deptno = 10 and his salary > 2000? No, so we take the first ELSE
2. v_newgoal := 1300 * 1.07 = 1391.
3. Is Miller's deptno = 20 and his salary > 2500? No, so we take the second ELSE
4. v_newgoal := 1300 * 1.055 = 1371.50
5. Is Miller's deptno = 30 and his salary > 1000? No, so we take the third ELSE
6. v_newgoal := 1300 * 1.065 = 1384.50

So the result your code will produce given this input is 1384.50, which is precisely what you're getting.

Obviously something needs to be changed here, but since we don't have the full statement of the problem you're trying to solve there's not much we can do to suggest what those changes need to be. But hopefully this points you in the right direction.

Best of luck.

Answer 2

Im not sure of your logic but maybe u should try with this kind of structure for your IF Then ELSE

IF(rc.deptno = 10)Then
    IF(rc.sal > 2000)Then
        v_newgoal := rc.sal * 1.06;
    ELSE 
        v_newgoal := rc.sal * 1.07; 
    END IF;    
END IF;

IF(rc.deptno = 20)Then
    IF(rc.sal > 2500)Then
        v_newgoal := rc.sal * 1.05;
    ELSE 
        v_newgoal := rc.sal * 1.055; 
    END IF;
END IF;

IF(rc.deptno = 30)Then
    IF(rc.sal > 1000)Then     
        v_newgoal := rc.sal * 1.07;
    ELSE 
        v_newgoal := rc.sal * 1.065; 
    END IF;    
END IF;