8 Replies Latest reply: May 1, 2013 8:14 AM by Ramani_apex RSS

    IR report value from function error

    Ramani_apex
      i have ir report and one column value coming from function,when open the IR report its giving error..
      .
      IR report code
      SELECT CS_ID ,CS_NAME, Util_func(PASS_ID) as "ALERT_DAYS"   from "CSD_MASTERS" 
      Function code
      FUNCTION  Util_func(PASS_ID NUMBER) RETURN number
        IS
      ALERT_DAYS NUMBER:=0;
         
        BEGIN
         select TO_DATE(CDS_DATE,'DD-MM-YYYY') - TO_DATE(SYSDATE,'DD-MM-YYYY')   
        INTO ALERT_DAYS
         from   CDS_TABBLE 
       where   CDS_ID=PASS_ID AND  
       CDS_DATE   between 
         sysdate and sysdate+15 
         group by  CDS_DATE  HAVING COUNT(*)  <=1  ;  
             
           IF RECCOUNT>0 THEN
              RETURN ALERT_DAYS;
           ELSE
             RETURN 'null';
           END IF;
      
        END;
      function calling CDS_TABBLE value

      CDS_ID -- CDS_DATE
      123 -- -- 4/23/2013
      124 -- -- 4/24/2013
      125 -- -- 4/25/2013


      Thanx,
      Ram

      Edited by: Ramani_vadakadu on Apr 11, 2013 8:56 PM