Stored Procedure, assigning same interest_rate value

[bebop]

Hi,

Have an application that calculates the Total interest of
a loan for a certain age and will display the result in a
Label. (Label1.Text = "Your interest due is " + "$" +
(String)Session["str"] + ""

When a user enters an age number in TextBox2 that equals
minimum_age value (i.e. 21), the stored procedure runs
with interest_rate of 7

Here's what I'm trying to do:

If a user enters a minimum_age number that's between 21
and 30 the interest_rate is the same as age 21
(interest_rate of 7).

How can I give the same interest_rate as age 21
(interest_rate of 7)if a user enters an age number in
TextBox2 between age 21 and 30 that doesn't equal any
minimum_age value in the table?

Same goes if a user enters a minimum_age number between 30
and 40 the interest_rate used is 6.5.

Any help would be appreciated.

bebop


Table age_tab:

create table age_tab(Minimum_Age number(4), Interest_Rate
LONG);

insert into age_tab values (21, 7);
insert into age_tab values (30, 6.5);
insert into age_tab values (40, 5.5);
insert into age_tab values (50, 5);

commit;

Stored Procedure ageloan_sp:

CREATE OR REPLACE procedure AGELOAN_SP
(a in number, b in number, x out varchar2)
as
q varchar2(4);
begin

select Interest_Rate into q from age_tab where
minimum_age=a;

x := q * b;
end;



code:

private void Button1_Click(object sender, System.EventArgs
e)
{
OracleCommand cmd = new OracleCommand("ageloan_sp",
oracleConnection1);
cmd.Parameters.Add
("a", OracleType.Number, 4).Value = TextBox2.Text;
cmd.Parameters.Add
("b", OracleType.Number, 4).Value = TextBox1.Text;
cmd.Parameters.Add
("x", OracleType.VarChar, 6);
cmd.Parameters
["x"].Direction = ParameterDirection.Output;

cmd.CommandType =
CommandType.StoredProcedure;

oracleConnection1.Open();
cmd.ExecuteNonQuery
();

if (TextBox2.Text !="")
{
String str = "";
str =
cmd.Parameters["x"].Value.ToString();

Session.Add
("str", str);

Server.Transfer("Two.aspx");
}
else
{
}
}
catch(OracleException ex)
{
}
finally
{

oracleConnection1.Close();
}



}

 

[Neil McKechnie]

I think that what you need is something like this:


select Interest_Rate into q from age_tab where
minimum_age=(
select min(minimum_age) from age_tab
where a >= minimum_age);


The sub-query finds the age range into which the parameter
falls, then the main query finds the matching interest
rate.

If you prefer this could be done in two separate queries.

Hope this helps,

Neil.

 

[bebop]

I tried the new stored procedure-close.

I entered 21 in the loan (textbox1) and for minimum_age
(textbox2) entered 21.

Did get the right answer of Interest due $147

Then entered 21 in the loan (textbox1) and for minimum_age
(textbox2) entered 24.

Now we have a minimum_age number that doesn't exist in the
table and I want the same interest_rate as minimum_age 21
(which is 7) assigned to minimum_ages between 21 and 29

The answer would be $147 and I did get that result.

To make sure it would work for the other ages: I entered
21 in the loan (textbox1) and for minimum_age (textbox2)
entered 30.

Now we have the interest_rate for minimum_age 30 as 6.5
The right answer would be $136.5

I kept getting $147 no matter what I put in.

Maybe I should do if statements in the code behind.

Can if statements be included in the stored procedure?

bebop

-----Original Message-----
I think that what you need is something like this:


select Interest_Rate into q from age_tab where
minimum_age=(
select min(minimum_age) from age_tab
where a >= minimum_age);


The sub-query finds the age range into which the parameter
falls, then the main query finds the matching interest
rate.

If you prefer this could be done in two separate queries.

Hope this helps,

Neil.

-----Original Message-----
Hi,

Have an application that calculates the Total interest of
a loan for a certain age and will display the result in a
Label. (Label1.Text = "Your interest due is " + "$" +
(String)Session["str"] + ""

When a user enters an age number in TextBox2 that equals
minimum_age value (i.e. 21), the stored procedure runs
with interest_rate of 7

Here's what I'm trying to do:

If a user enters a minimum_age number that's between 21
and 30 the interest_rate is the same as age 21
(interest_rate of 7).

How can I give the same interest_rate as age 21
(interest_rate of 7)if a user enters an age number in
TextBox2 between age 21 and 30 that doesn't equal any
minimum_age value in the table?

Same goes if a user enters a minimum_age number between 30
and 40 the interest_rate used is 6.5.

Any help would be appreciated.

bebop


Table age_tab:

create table age_tab(Minimum_Age number(4), Interest_Rate
LONG);

insert into age_tab values (21, 7);
insert into age_tab values (30, 6.5);
insert into age_tab values (40, 5.5);
insert into age_tab values (50, 5);

commit;

Stored Procedure ageloan_sp:

CREATE OR REPLACE procedure AGELOAN_SP
(a in number, b in number, x out varchar2)
as
q varchar2(4);
begin

select Interest_Rate into q from age_tab where
minimum_age=a;

x := q * b;
end;



code:

private void Button1_Click(object sender, System.EventArgs
e)
{
OracleCommand cmd = new OracleCommand("ageloan_sp",
oracleConnection1);
cmd.Parameters.Add
("a", OracleType.Number, 4).Value = TextBox2.Text;
cmd.Parameters.Add
("b", OracleType.Number, 4).Value = TextBox1.Text;
cmd.Parameters.Add
("x", OracleType.VarChar, 6);
cmd.Parameters
["x"].Direction = ParameterDirection.Output;

cmd.CommandType =
CommandType.StoredProcedure;

oracleConnection1.Open();
cmd.ExecuteNonQuery
();

if (TextBox2.Text !="")
{
String str = "";
str =
cmd.Parameters["x"].Value.ToString();

Session.Add
("str", str);

Server.Transfer("Two.aspx");
}
else
{
}
}
catch(OracleException ex)
{
}
finally
{

oracleConnection1.Close();
}



}

.

.