CODEWITHSUNDEEP
sqlstored-functions
Oscar
Oscar

Reputation: 69

SQL statement with case

I want to have a function that receives a salary value and a job_id. This function needs to verify if that salary value passed as a parameter is between the max and min salary.

CREATE FUNCTION check(revenue Number, id integer) 
RETURN message 
IS message varchar2;

BEGIN  

select min(revenue), max(revenue) from  users;


RETURN(message);

END;

Upvotes: 0

Views: 56

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You can create a function without returning local variables for extremum values as an option through use of analytic functions :

CREATE OR REPLACE FUNCTION verifyIfSalaryIsBetweenMinAndMaxForThatJob(i_salary NUMBER,
                                                                      i_jobId  INT)
                    RETURN VARCHAR2 IS
  message VARCHAR2(50);
BEGIN
  SELECT MAX(CASE WHEN i_salary < min_sal THEN 
                  'Below' 
              WHEN i_salary > max_sal THEN 
                  'Above Max'     
              WHEN i_salary BETWEEN min_sal AND max_sal THEN 
                  'Between'                                 
          END) 
    INTO message
    FROM
    (
      SELECT MIN(salary) OVER (PARTITION BY job_Id) AS min_sal,
             MAX(salary) OVER (PARTITION BY job_Id) AS max_sal,
             e.*
        FROM employees e
    )
   WHERE job_id = i_jobId; 

  RETURN message;

END;
/

Demo

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142798

Unless message is a datatype (and it probably isn't), you can't return it. What you do want to return is a string - varcahr2 datatype.

So:

SQL> create or replace function f_test (salary in number)
  2    return varchar2
  3  is
  4    l_min number;
  5    l_max number;
  6  begin
  7    select min(sal), max(sal)
  8      into l_min, l_max
  9      from emp;
 10
 11    return case when salary between l_min and l_max then 'Between'
 12                when salary > l_max then 'Above max'
 13                else 'Other'
 14           end;
 15  end;
 16  /

Function created.

SQL> select f_test(6000) result from dual;

RESULT
-------------------------------------------------------------------------------
Above max

SQL>

Feel free to improve it (by adding jobs, departments, whatnot).

Upvotes: 1

Related Questions