Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2009
    Posts
    46

    Unanswered: return Nothing in Oracle function

    Hi,

    I am writing a function which is returning a count of employee on join with employee inout table,but if any employee have no intry in the inouttable then function is returning nothing but in such case i want to return 0.
    can anyone please suggest me how to handle Nothing condition in oracle

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Without seeing your code I can't comment on what is wrong with it. This function would return 0 when there are no employees, and a larger number if there are:

    Code:
    create or replace function count_emps return integer
    is
      l_count integer;
    begin
      select count(*)
      into   l_count
      from   emp
      join inout on inout.empno = emp.empno;
    
      return l_count;
    end;
    What does your code do?

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As COUNT function returns number of records (which is 0 (zero) or higher) and it can not return NULL (see the documentation), you'll have to create your own logic which will decipher what result of the COUNT function equal to 0 means (and return 0 or NULL).

  4. #4
    Join Date
    Jun 2009
    Posts
    46
    hi,

    Below is the code :


    CREATE OR REPLACE FUNCTION Fn_Gettest
    (
    p_employeeid INT,
    p_fromdate DATE,
    p_todate DATE

    )
    RETURN INT
    AS
    PresentDays INT;
    BEGIN
    SELECT COUNT(*) INTO PresentDays FROM TBL_EMPLOYEES
    right Join INOUTMSTR ON INOUTMSTR.employeeid = TBL_EMPLOYEES.employeeid
    WHERE TRUNC(TO_DATE(inout_date,'DD/MM/yyyy')) >= TRUNC(TO_DATE(p_fromdate,'DD/MM/YYYY')) AND
    TRUNC(TO_DATE(inout_date,'DD/MM/yyyy')) <= TRUNC( TO_DATE(p_todate ,'DD/MM/YYYY') )
    AND TBL_EMPLOYEES.employeeid=p_employeeid AND TBL_EMPLOYEES.ISDELETED ='N'
    AND TBL_EMPLOYEES.ISACTIVE =0 AND TBL_EMPLOYEES.ISSETTLEMENT=0
    GROUP BY TBL_EMPLOYEES.employeeid ORDER BY TBL_EMPLOYEES.employeeid;

    RETURN PresentDays ;
    END Fn_Gettest;
    /


    Thanks for the Reply!

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There are many issues with that code. The one that prevents you getting a 0 is the redundant GROUP BY. Other issues:
    - ORDER BY is pointless when query will always return 1 row
    - Right (Outer) Join is pointless when you are referencing the Outer table in the WHERE clause so it has to be returning a row (Inner Joint) anyway
    - You must never TO_DATE a value that is already a date (e.g. p_fromdate).

    Try:
    Code:
    CREATE OR REPLACE FUNCTION Fn_Gettest
    ( p_employeeid INT,
      p_fromdate DATE,
      p_todate DATE
    )
    RETURN INT
    AS
      PresentDays INT;
    BEGIN
      SELECT COUNT(*) INTO PresentDays FROM TBL_EMPLOYEES
      Join INOUTMSTR ON INOUTMSTR.employeeid = TBL_EMPLOYEES.employeeid
      WHERE TRUNC(inout_date) >= TRUNC(p_fromdate) AND
      TRUNC(inout_date) <= TRUNC( p_todate )
      AND TBL_EMPLOYEES.employeeid=p_employeeid AND TBL_EMPLOYEES.ISDELETED ='N'
      AND TBL_EMPLOYEES.ISACTIVE =0 AND TBL_EMPLOYEES.ISSETTLEMENT=0;
    
      RETURN PresentDays ;
    END Fn_Gettest;
    /
    (Also BTW, prefixing your table names with "TBL_" is laughed at in Oracle circles! )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •