This discussion is archived
9 Replies Latest reply: Mar 8, 2013 1:31 AM by user588120

# Recursive  Function

Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
>
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
Currently Being Moderated
hi
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
Currently Being Moderated
Hi,

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
Currently Being Moderated
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
Currently Being Moderated
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.

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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
Thank you for the reply,Any way there are some people who is able to understand my question and give correct answers

#### Legend

• Correct Answers - 10 points