CODEWITHSUNDEEP
oracle-databasefunctionplsql
Ragav
Ragav

Reputation: 229

Return NULL when no matching records are found

I'm passing an employee number to a function and getting back the Joined Date.

I want this to return NULL when no matching records found; in my case it is just returning blank or an empty row;

get_join_date(in_emp_no)

CREATE OR REPLACE FUNCTION get_join_date(in_emp_no) RETURN DATE IS
  v_join_date DATE;
  BEGIN
    SELECT joined_date
    INTO v_date
    FROM employee
    WHERE employee_number = in_emp_no
          AND type = in_type;

    IF v_join_date IS NOT NULL        THEN
      v_join_date := v_date;
    ELSE
      v_join_date = NULL;
    END IF;

    RETURN v_join_date;
  END;

Upvotes: 0

Views: 1005

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

As you want to return either the one existing value or null, you can simply use an aggregate function, e.g. MIN, for that:

CREATE OR REPLACE FUNCTION get_join_date( in_emp_no) RETURN DATE
IS
  v_join_DATE DATE;
BEGIN
  SELECT MIN(JOINED_DATE)
    INTO v_join_DATE 
    FROM employee
   WHERE employee_number = in_emp_no
     AND TYPE=in_type;
  return v_join_DATE;
end

Upvotes: 2

Ben
Ben

Reputation: 52923

If no matching records are found then a NO_DATA_FOUND exception will be raised; you have to capture this exception and return something.

You're also checking whether v_join_date is null and then assigning a null value to it if it is null; there's no need to do this.

create or replace function get_join_date( 
      Pemp_no in number
     ) return date is

   l_join_date date;

begin

   select joined_date into l_join_date 
     from employee
    where employee_number = Pemp_no
      and type = in_type;

   return l_join_date;

exception when no_data_found then
   return null;

end;

Other errors include:

  1. You're selecting into v_date but you declared your variable as v_join_date
  2. There's no semi-colon after your end statement.
  3. There's no semi-colon after your endif statement.
  4. endif is two words, this should be end if;
  5. You're not declaring the datatype of the parameter in_emp_no you need to (but not the length), for instance ... function get_join_date ( Pemp_no number ) ...

Upvotes: 5

Related Questions