6 Replies Latest reply: Dec 27, 2012 8:11 AM by Vigneswar Battu RSS

    To_Number conversion in Fast Formula

    877722
      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
          iavinash.com
          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
            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
              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
                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
                  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
                    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