ERROR shows in the formula Colomun

Sujith S
Sujith S used Ask the Experts™
on
we have created a formula placeholder to show the working days between 2 dates but it gives an error as invalid number attached the RDF and the error image
please help us to resolve the isssue



function CF_1Formula return Number is
m_acal_dr number;
m_acal_cr number;
CURSOR C1 IS
select distinct (workdays) from (
SELECT   :r_from_cust_main_acnt_code,:r_till_cust_main_acnt_code,
           (:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code)
         - 2 * FLOOR ((:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code) / 7)
          AS workdays
    FROM ot_job_exec_head);
 
begin
           IF C1%ISOPEN THEN
        CLOSE C1;
  END IF;
 
  OPEN C1;
  FETCH C1 INTO M_acal_dr;
  CLOSE C1;
   
  return( M_acal_dr);
 
  end;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Can't open the RDF but if you can post code, I'll take a look.

Author

Commented:
function CF_1Formula return Number is
m_acal_dr number;
m_acal_cr number;
CURSOR C1 IS
select distinct (workdays) from (
SELECT   :r_from_cust_main_acnt_code,:r_till_cust_main_acnt_code,
           (:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code)
         - 2 * FLOOR ((:r_till_cust_main_acnt_code - :r_from_cust_main_acnt_code) / 7)
          AS workdays
    FROM ot_job_exec_head);
 
begin
           IF C1%ISOPEN THEN
        CLOSE C1;
  END IF;
 
  OPEN C1;
  FETCH C1 INTO M_acal_dr;
  CLOSE C1;
   
  return( M_acal_dr);
 
  end;



input is 01-01-2018 -31-01-2018

ouput -22
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Assuming  :r_from_cust_main_acnt_code is the variable to hold the inputs.

If the input is a string then you need to use TO_DATE to convert it into a date data type before subtracting them:
to_date( :r_from_cust_main_acnt_code,'DD-MM-YYYY')

Same for the other one.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial