This discussion is archived
1 Reply Latest reply: Jul 8, 2013 2:38 AM by Archana.D RSS

Return unique values through farmula column

c44b6583-b7de-497c-bc35-fc56bfe9fff5 Newbie
Currently Being Moderated

I am facing a problem in leave_form report!

want to show employee's previous leaves detail (leavedate,leavetype), i don't want to show leavetype 'PPP'

repeating frame type is Across/Down, there are 22 records of leavetype 'PPP' and one record of leavetype 'CL'

problem is report is showing 22 records of leavetype 'CL' of same leavedate!

i want that report should show the actual leavedate and leavetype records.

using 9i database 6i developer server2003



For leavetype

FUNCTION Cf_1formula
RETURN CHAR
IS
  v_leavetype CHAR(40);
BEGIN
    SELECT LT.description
    INTO   v_leavetype
    FROM   hrm_attendance L,
           hrm_leavetype LT,
           hrm_employees E
    WHERE  E.employeecode = L.employeecode
           AND L.employeecode = :EMPLOYEECODE
           AND L.leavetype = LT.leavetype
           AND L.leavetype NOT IN ( 'PPP' );

    RETURN v_leavetype;
EXCEPTION
  WHEN OTHERS THEN
             RETURN NULL;
END;

For Leavedate

FUNCTION Cf_2formula
RETURN DATE
IS
  v_leavedate DATE;
BEGIN
    SELECT L.leavedate
    INTO   v_leavedate
    FROM   hrm_attendance L,
           hrm_leavetype LT,
           hrm_employees E
    WHERE  E.employeecode = L.employeecode
           AND L.employeecode = :EMPLOYEECODE
           AND L.leavetype = LT.leavetype
           AND L.leavetype NOT IN ( 'PPP' );

    RETURN v_leavedate;
EXCEPTION
  WHEN OTHERS THEN
             RETURN NULL;
END; 


  • 1. Re: Return unique values through farmula column
    Archana.D Newbie
    Currently Being Moderated

    Hi,

    So as per your requirement you need to display records which belongs to leave Type 'CL' right?

    If so assume my sample data and code below...

    So now come up what's wrong with your formula column? Because the sample code plays the Same functionality of your formula ..

     

    SQL Statement which produced this data:
      with hrm_attendance
              as (select '6/1/2013' leavedate, 000 employeecode, 'CL' leavetype
                    from dual
                  union all
                  select '6/3/2013' leavedate, 001 employeecode, 'PPP' leavetype
                    from dual
                  union all
                  select '6/5/2013' leavedate, 002 employeecode, 'PPP' leavetype
                    from dual
                  union all
                  select '6/7/2013' leavedate, 003 employeecode, 'PPP' leavetype
                    from dual
                  union all
                  select '6/6/2013' leavedate, 004 employeecode, 'PPP' leavetype
                    from dual),
           hrm_leavetype as (select 'sick leave' des, 'CL' leavetype from dual
                             union all
                             select 'party' des, 'PPP' leavetype from dual),
           hrm_employee as (select 000 employeecode from dual
                            union all
                            select 001 employeecode from dual
                            union all
                            select 002 employeecode from dual
                            union all
                            select 003 employeecode from dual
                            union all
                            select 004 employeecode from dual)
      select l.leavedate Leave_date, lt.des description
        from hrm_attendance l, hrm_leavetype lt, hrm_employee e
       where     e.employeecode = l.employeecode
             and l.employeecode = nvl (:employeecode, l.employeecode)
             and l.leavetype = lt.leavetype
             and l.leavetype not in ('PPP')
    O/P:
    LEAVE_DATE,DESCRIPTION
    '6/1/2013','sick leave'

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points