9 Replies Latest reply: Mar 8, 2013 3:31 AM by user588120 RSS

    Recursive  Function

    user588120
      Hi All


      Amount=10000
      Rate of Interest=12%
      Period =3Months

      and get the output 300 as Interest
      My requirement is I need to find out the Interest of Amount+Interest (10000+300)
      say
      Amount=10300
      Rate of Interest=13%
      Period =2Months

      Is it possible to get both the result without using loop
        • 1. Re: Recursive  Function
          sb92075
          user588120 wrote:
          Hi All


          Amount=10000
          Rate of Interest=12%
          Period =3Months

          and get the output 300 as Interest
          My requirement is I need to find out the Interest of Amount+Interest (10000+300)
          say
          Amount=10300
          Rate of Interest=13%
          Period =2Months

          Is it possible to get both the result without using loop
          I don't understand.
          1) What are the inputs to this function?
          2) What formula needs to be implemented within this function?
          3) What is the single results value to be returned by this function?

          is the interest compounded daily, weekly, monthly, or annually?


          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Recursive  Function
            rp0428
            >
            Amount=10000
            Rate of Interest=12%
            Period =3Months

            and get the output 300 as Interest
            My requirement is I need to find out the Interest of Amount+Interest (10000+300)
            say
            Amount=10300
            Rate of Interest=13%
            Period =2Months

            Is it possible to get both the result without using loop
            >
            Yes - just apply the standard formula for compound interest. There are plenty of links on the net that have it. Here is one.
            http://en.wikipedia.org/wiki/Compound_interest
            • 3. Re: Recursive  Function
              user588120
              hi
              Forget about compound interest
              What my request is

              I have two records


              Amount=10000
              Rate of Interest=12%
              Period =3Months
              Output=300

              Amount=10300 (This is the actual amount (10000)+ interest (300))
              Rate of Interest=13%
              Period =2Months
              output=223

              So total Interest=300+223

              Is it possible to get this result without using cursor or loop
              • 4. Re: Recursive  Function
                ragu.dba.in
                Hi,

                Here is your SQL...

                select a.First_int_amount,(a.First_int_Amount+&Amount)*(&Second_ROI/100/12)*&second_Period Second_int_amount from (select &amount*(First_ROI/100/12)*&First_period First_int_amount from dual) a;

                Amount = 10000
                First_ROI =12
                First_Period =3

                Amount=10000
                Second_ROI =13
                Second_Period =2

                Result as First_int_amount=300 and second_int_amount=223.166667

                Though the Query structure woeld confuse, it is correctt one.use it in PLSQL block with execute immediate with proper input parameters and bind varibales it would be clearer to understand.

                Regards,
                ragunath.
                • 5. Re: Recursive  Function
                  user588120
                  Thank you for your valuable reply,but issue is that sometimes it may be two records sometimes it may 3 or more

                  This is actually an interest computation program and the computation depends on the months completed
                  We have a slab say for 0-3 months the int.rate is 12 %
                  for 3-6 it's 13% and 6-9 14% etc etc

                  Suppose if the customer completed 8 moths Interest should have like this
                  First 3 months Amount*12%*3 months=x
                  Next 3 Months (Amount+x)*13%*3 months=y
                  Next 2 months (Amount+x+y)*14%*2 months= Z

                  So Z is final Interest Amount

                  Can i get the result (Z) in a single query (not using any loop)

                  Edited by: user588120 on Mar 7, 2013 10:12 PM
                  • 6. Re: Recursive  Function
                    BluShadow
                    user588120 wrote:
                    Thank you for your valuable reply,but issue is that sometimes it may be two records sometimes it may 3 or more
                    No, the issue is that you have not asked your question properly.
                    People here don't know what is known data and what data is trying to be calculated. From your question it looks like you want to somehow calculate the interest amount, and then somehow sum that as a running sum (compound interest).

                    Perhaps if you supplied us with some example data in the form of create table and insert statements, and showed what output you're expecting from that example data, we could get a better idea what you mean.

                    By now, as a member of these forums for over 5 years, and lost of posts under your belt, you should know how to go about asking a question and supplying enough information for people to help you.

                    Take a read of {message:id=9360002}

                    If you don't ask questions properly, then people will struggle to help you properly. That could be a reason why you still have so many unanswered questions:

                    >
                    Total Questions: 69 (51 unresolved)
                    >

                    or that could just be because you haven't bothered to mark questions answered when they have been, as per forum etiquette.
                    • 7. Re: Recursive  Function
                      ragu.dba.in
                      Hi,

                      why cant you create a procedure and pass any number of input records (all levels of slab) through parameters in a array.? (procedure would definitely use loop).

                      Because you said,
                      sometimes it may be two records sometimes it may 3 or more
                      it requires iteration to process variable number of slab (and to hold the value of interest amount) each time.by the way, why you dont want it in a loop.?

                      regards,
                      ragunath.
                      • 8. Re: Recursive  Function
                        BluShadow
                        ragu.dba.in wrote:
                        Hi,

                        why cant you create a procedure and pass any number of input records (all levels of slab) through parameters in a array.? (procedure would definitely use loop).

                        Because you said,
                        sometimes it may be two records sometimes it may 3 or more
                        it requires iteration to process variable number of slab (and to hold the value of interest amount) each time.by the way, why you dont want it in a loop.?
                        Loops in PL/SQL are slow if you're having to process large amounts of data, so if possible one should aim to use SQL where possible.

                        Now, assuming the OP is using 11gR2 which supports recursive subquery factoring, and also making some assumptions about the data available, he could be looking to do something like...
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1  with int_period as (select 1 as period, 3 as months, 12 as perc from dual union all
                          2                      select 2, 3, 13 from dual union all
                          3                      select 3, 2, 14 from dual union all
                          4                      select 4, 4, 15 from dual)
                          5      ,cust_data  as (select 1 as cust_id, 10000 as amount from dual union all
                          6                      select 2, 7500 from dual)
                          7  --
                          8  -- end of test data
                          9  --
                         10      -- and now for the recursive subquery factoring
                         11     ,interest(cust_id, period, amount) as
                         12                     (/* anchor query */
                         13                      select cust_id, 0, amount from cust_data
                         14                      union all
                         15                      /* recursive query */
                         16                      select interest.cust_id
                         17                            ,interest.period+1 as period
                         18                            ,interest.amount+(interest.amount*int_period.months*(int_period.perc/100)) as amount
                         19                      from   interest
                         20                             join int_period on (int_period.period = interest.period+1)
                         21                      where  interest.period <= (select max(period) from int_period)
                         22                     )
                         23  --
                         24  select cust_id, period, lpad(to_char(amount,'fm9999999.00'),10,' ') as amount
                         25  from   interest
                         26* order by cust_id, period
                        SQL> /
                        
                           CUST_ID     PERIOD AMOUNT
                        ---------- ---------- ----------
                                 1          0   10000.00
                                 1          1   13600.00
                                 1          2   18904.00
                                 1          3   24197.12
                                 1          4   38715.39
                                 2          0    7500.00
                                 2          1   10200.00
                                 2          2   14178.00
                                 2          3   18147.84
                                 2          4   29036.54
                        
                        10 rows selected.
                        • 9. Re: Recursive  Function
                          user588120
                          Thank you for the reply,Any way there are some people who is able to understand my question and give correct answers