This discussion is archived
6 Replies Latest reply: Dec 27, 2012 6:11 AM by Vigneswar Battu RSS

To_Number conversion in Fast Formula

877722 Newbie
Currently Being Moderated
Hi All

I am doing a simple Fast Formula calculation where I'm trying to convert the return date into number.
It is throwing error. But What am i doing wrong. I'm trying to convert the date returned value to number.



***
APP-FF-33983: Parameter type mismatch for function 'TO_NUMBER' at line 15

Cause: The user has specified a function whose name has been recognised but whose parameter types do not match any known version of the function.

Action: Please check that any variables or literals passed to the function are of the appropriate types for the function definition. ***

This is my code

----------------------------------------------------
DEFAULT FOR EMP_TERM_DATE IS '1-JAN-4712'(date)
DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '01-JAN-1951' (date)
DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '31-DEC-4712' (date)
DEFAULT FOR C_Allowance_Balance_ASG_YTD is 0
DEFAULT FOR L_paid_amount IS 0
DEFAULT FOR Day_amount IS 0
DEFAULT FOR days_deduction IS 0
DEFAULT FOR deducted_amount IS 0
DEFAULT FOR L_last_day is '31-JAN-4712'(date)

L_last_day = TO_DATE( '31-DEC-'+TO_CHAR(PAY_PROC_PERIOD_END_DATE,'YYYY'),'DD-MON-YYYY')

Day_amount = (C_Allowance_Balance_ASG_YTD)/ 365

/**15* */ days_deduction =to_number(l_last_day- EMP_TERM_DATE)
deducted_amount = days_deduction*days_amount

return deducted_amount
  • 1. Re: To_Number conversion in Fast Formula
    Avinash Journeyer
    Currently Being Moderated
    Hi,

    Why are you using to_number function. Any how, as per my understing, l_last_day- EMP_TERM_DATE will retun number value only.

    Please try without to_number function.

    Thanks,
    Avinash
  • 2. Re: To_Number conversion in Fast Formula
    877722 Newbie
    Currently Being Moderated
    Thanks Avinash,for the reply!

    Tried that also...
    this is the error i get.

    APP-FF-33003: Line 18: 'DAYS_DEDUCTION' is of type NUMBER but has DATE type assigned into it.

    Cause: The data type of the expression on the right hand side of the assignment statement does not match the data type of the variable on the left hand side of the assignment, probably because of an earlier assignment to the variable of a different data type.

    Action: Ensure data types of assignments match, or that variable being set has not been set before to another data type.

    This is my complete formula

    ------------------------------------------
    DEFAULT FOR EMP_TERM_DATE IS '1-JAN-4712'(date)
    DEFAULT FOR PAY_PROC_PERIOD_START_DATE IS '01-JAN-1951' (date)
    DEFAULT FOR PAY_PROC_PERIOD_END_DATE IS '31-DEC-4712' (date)
    DEFAULT FOR C_Allowance_Balance_ASG_YTD is 0
    DEFAULT FOR L_paid_amount IS 0
    DEFAULT FOR Day_amount IS 0
    DEFAULT FOR days_deduction IS 0
    DEFAULT FOR deducted_amount IS 0
    DEFAULT FOR L_last_day is '31-JAN-4712'(date)

    L_msg = 'Club Allowance: '

    L_last_day = TO_DATE( '31-DEC-'+TO_CHAR(PAY_PROC_PERIOD_END_DATE,'YYYY'),'DD-MON-YYYY')

    Day_amount = (C_Allowance_Balance_ASG_YTD)/ 365

    days_deduction = L_last_day-EMP_TERM_DATE
    deducted_amount = (days_deduction*day_amount)
    L_msg = L_msg||' Term Date :'||to_char(EMP_TERM_DATE)||'Days_ded: '||to_char(days_deduction)

    return deducted_amount,L_msg
  • 3. Re: To_Number conversion in Fast Formula
    Aneesh N Newbie
    Currently Being Moderated
    Hi,

    Try this once.

    days_deduction = days_between(L_last_day,EMP_TERM_DATE)

    Regards,
    Aneesh N
  • 4. Re: To_Number conversion in Fast Formula
    877722 Newbie
    Currently Being Moderated
    Thanks Aneesh..Thats working perfectly fine although I wonder why the first case shouldnt work!!
  • 5. Re: To_Number conversion in Fast Formula
    Aneesh N Newbie
    Currently Being Moderated
    Good that it worked. I too have no idea why the other is not working.
  • 6. Re: To_Number conversion in Fast Formula
    Vigneswar Battu Guru
    Currently Being Moderated
    Hi 874719,

    Fast Formula engine and the Sql Pl/sql engine are not one and the same.
    So you cannot do a subtract operation on dates.
    The only option is to use formula functions, in this case DAYS_BETWEEN.

    Also, there might be an issue with your code.
    I guess you should use +1 in the logic, else you would get 1 less day in the calculation.
    days_deduction = days_between(L_last_day,EMP_TERM_DATE) + 1
    Eg:
    SELECT TO_DATE('05-JAN-2012') - TO_DATE('01-JAN-2012') FROM DUAL;
    returns 4, when you might need 5.

    Cheers,
    Vignesh

Legend

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