2021年5月2日星期日

log returns the result of a fun function

I have a function that returns the amount of a term saver, but the code that I have, the system returns an error.

 CREATE OR REPLACE FUNCTION moneysaving       RETURN number       IS      moineysaving_p number(10);        BEGIN        select  customer_id , case          when periods_id = "TK12" then           case              when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =12             then TRUNC(money *(1+0.063/365*365))             when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >12 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <24              then TRUNC((money *(1+0.063/365*365))+(money *(1+0.003/365*365)))             when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=24             then TRUNC((money *(1+0.063/365*365))+(money *(1+0.06/365*365)))           end           when   periods_id = "TK6" then            case            when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =6            then TRUNC(money *(1+0.054/365*365)))            when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >6 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <12            then TRUNC((money *(1+0.054/365*365))+(money *(1+0.3/365*365)))            when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=12            then TRUNC((money *(1+0.054/365*365))+(money *(1+0.054/365*365)))           end          when   periods_id = "TK3" then             case             when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =3             then TRUNC(money *(1+0.05/365*365)))             when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >3|| TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <6             then TRUNC(money *(1+0.05/365*365))+(money *(1+0.003/365*365)))             when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=6             then TRUNC(money *(1+0.05/365*365))+(money *(1+0.05/365*365)))            end          when   periods_id = "TK1" then            case              when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) =1              then TRUNC(money *(1+0.045/365*365)))              when TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >1 || TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) <3              then TRUNC(money *(1+0.045/365*365))+money *(1+0.003/365*365)))              when  TRUNC(MONTHS_BETWEEN (TO_DATE (sentdate, 'yyyy/mm/dd'), TO_DATE ( SYSDATE(), 'yyyy/mm/dd'))) >=3              then TRUNC(money *(1+0.045/365*365))+money *(1+0.045/365*365)))            end           else TRUNC(money *(1+0.003/365*365))        end         from saving ;      RETURN moineysaving_p;      END;  

You have some money deposited in the bank for the term registered, you come get the money by term, and calculate the amount you received when depositing over that term, ora-00695, ora-001765

https://stackoverflow.com/questions/67363075/log-returns-the-result-of-a-fun-function May 03, 2021 at 11:09AM

没有评论:

发表评论