ERROR shows in the formula Colomun

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;
Sujith SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can't open the RDF but if you can post code, I'll take a look.
0
Sujith SAuthor 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
0
slightwv (䄆 Netminder) 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
oracle reports

From novice to tech pro — start learning today.