CODEWITHSUNDEEP
sqloracle-databaseplsqlcase
user6611026
user6611026

Reputation:

What is wrong with my CASE statement on PLSQL?

I'm coding a simple CASE to print on screen when a employee earn more of the AVG salary of the department. But I don't understand why I see on screen only one result when I want to check salary of 2 employees. I hope you understand my question. Thanks.

SET SERVEROUTPUT ON

DECLARE
  v_emp121_sal   employees.salary%TYPE;
  v_emp121_lname employees.last_name%TYPE;

  v_emp139_sal   employees.salary%TYPE;
  v_emp139_lname employees.last_name%TYPE;

  v_avgsal       employees.salary%TYPE;
BEGIN
  SELECT salary, last_name
  INTO   v_emp121_sal, v_emp121_lname
  FROM   employees
  WHERE  employee_id = 121;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE 121 SALARY IS: ' || v_emp121_sal);

  SELECT salary, last_name
  INTO   v_emp139_sal, v_emp139_lname
  FROM   employees
  WHERE  employee_id = 139;
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE 139 SALARY IS: ' || v_emp139_sal);

  SELECT AVG(salary)
  INTO   v_avgsal
  FROM   employees
  WHERE  department_id = 50;
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT 50 AVG SALARY IS: ' ||
                          TRUNC(v_avgsal, 0));


  CASE
      WHEN 
          v_emp121_sal < v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp121_lname ||
                               ' IS LESS THAN DEPARTMENT AVG');
      WHEN
          v_emp121_sal > v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp121_lname ||
                               ' IS HIGHER THAN AVG DEPARTMENT');
      WHEN
          v_emp139_sal < v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp139_lname ||
                               ' IS LESS THAN DEPARTMENT AVG');
      WHEN
          v_emp139_sal > v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp139_lname ||
                               ' IS HIGHER THAN AVG DEPARTMENT');
  END CASE;

END;
/

enter image description here

Theory, I should receive on screen "SALARY OF EMP FRIPP IS HIGHER THAN AVG DEPARTMENT" "SALARY OF EMP SEO IS LESS THAN AVG DEPARTMENT"

Upvotes: 0

Views: 244

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

A case expression returns one value. If you need separate outputs for two employees, use 2 case expressions so each of them returns its own value.

CASE
      WHEN 
          v_emp121_sal < v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp121_lname ||
                               ' IS LESS THAN DEPARTMENT AVG');
      WHEN
          v_emp121_sal > v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp121_lname ||
                              ' IS HIGHER THAN AVG DEPARTMENT');
END CASE;

CASE
      WHEN
          v_emp139_sal < v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp139_lname ||
                               ' IS LESS THAN DEPARTMENT AVG');

      WHEN
          v_emp139_sal > v_avgsal THEN
          DBMS_OUTPUT.PUT_LINE('SALARY OF EMP ' || v_emp139_lname ||
                               ' IS HIGHER THAN AVG DEPARTMENT');
END CASE;

You might want to add a condition for = as well so you can avoid a null output when > or < condition fails.

Upvotes: 2

Related Questions