1 Reply Latest reply: Jul 8, 2013 4:38 AM by Archana.D RSS

    Return unique values through farmula column

    c44b6583-b7de-497c-bc35-fc56bfe9fff5

      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

          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'