Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complex Query

Status
Not open for further replies.

mj11

Technical User
Apr 11, 2007
5
AU
Ok I have the following variables

Discount Rate (R) - A static user defined value
demand (D) - the result of a query
base year (Y1) - 2003
future year (Y2) - user entered and needs to count backward to base year

I need the following formula to execute

NPD = D/ (1+R)^(Y2-Y1)

and iterate over taking 1 off of the Y2 value until until (which the user entered) = Y1 (2003). Each time it iterates it needs to go off and select D again based on the new iterated smaller value of Y2, figure out NPD and add the value to the earlier one.

So if the user enters 2009 for Y2 the query needs to go and run the select statement I build for D and then use that to figure out NPD, ie NPD = D/ (1+R)^(2009 - 2003).

Then it needs to set Y2 to to 2008 and recalculate NPD in the same manner.

Each value for NPD needs to be stored and added to the others. So if it iterates 8 times I need the result to be the sum of the 8 iterations.

Complex enough? I'm working with SQL in Oracle/ HTMLDB/ Application Express. Anyone help me here?

Thanks, Michael


 
What have you tried so far?

Ignorance of certain subjects is a great part of wisdom
 
Alex thanks.

I've only got as far as writing 33 different queries for each possible value of Y2, which will be between 2003 and 2036. This approach will be fine but I need help getting this one to run as well.

So I have a value for Y2 which will trigger one of 33 possible queries. How can I set this up?

IF Y2 = '2003'

THEN SELECT (query)

IF Y2 = '2004'

THEN SELECT (query)

What is the best syntax for this and how should it look? I tried IFs and WHEREs to no avail. Not that they won't work, I just can't get them to work. I'm very new to SQL (first time I've ever used it) so this is a crash (and burn) course for me.

Thanks for your time
 
Can you post an example of your select query?

Also, what database are you using (and it looks like a front end application as well, what is written with)?

I'm about 93% certain there is a better way to do this :)

HOpe this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I'm 110% sure there is Alex :) :lol: Yes it helps.... very much! It is being written in HTMLDB/ Application Express, which as you rightly say is a middleware/ front end for deploying web apps reading from Oracle.

Here is the select query.

select
(D2004 /(POWER((1 + :)DISC_1/100)),(2004 - 2003))))+
(D2005 /(POWER((1 + :)DISC_1/100)),(2005 - 2003))))+
(D2006 /(POWER((1 + :)DISC_1/100)),(2006 - 2003))))+
(D2007 /(POWER((1 + :)DISC_1/100)),(2007 - 2003))))+
(D2008 /(POWER((1 + :)DISC_1/100)),(2008 - 2003))))+
(D2009 /(POWER((1 + :)DISC_1/100)),(2009 - 2003))))+
(D2010 /(POWER((1 + :)DISC_1/100)),(2010 - 2003))))+
(D2011 /(POWER((1 + :)DISC_1/100)),(2011 - 2003))))+
(D2012 /(POWER((1 + :)DISC_1/100)),(2012 - 2003))))+
(D2013 /(POWER((1 + :)DISC_1/100)),(2013 - 2003))))+
(D2014 /(POWER((1 + :)DISC_1/100)),(2014 - 2003))))+
(D2015 /(POWER((1 + :)DISC_1/100)),(2015 - 2003))))+
(D2016 /(POWER((1 + :)DISC_1/100)),(2016 - 2003))))+
(D2017 /(POWER((1 + :)DISC_1/100)),(2017 - 2003))))+
(D2018 /(POWER((1 + :)DISC_1/100)),(2018 - 2003))))+
(D2019 /(POWER((1 + :)DISC_1/100)),(2019 - 2003))))+
(D2020 /(POWER((1 + :)DISC_1/100)),(2020 - 2003))))+
(D2021 /(POWER((1 + :)DISC_1/100)),(2021 - 2003))))+
(D2022 /(POWER((1 + :)DISC_1/100)),(2022 - 2003))))+
(D2023 /(POWER((1 + :)DISC_1/100)),(2023 - 2003))))+
(D2024 /(POWER((1 + :)DISC_1/100)),(2024 - 2003))))+
(D2025 /(POWER((1 + :)DISC_1/100)),(2025 - 2003))))+
(D2026 /(POWER((1 + :)DISC_1/100)),(2026 - 2003))))+
(D2027 /(POWER((1 + :)DISC_1/100)),(2027 - 2003))))+
(D2028 /(POWER((1 + :)DISC_1/100)),(2028 - 2003))))+
(D2029 /(POWER((1 + :)DISC_1/100)),(2029 - 2003))))+
(D2030 /(POWER((1 + :)DISC_1/100)),(2030 - 2003))))+
(D2031 /(POWER((1 + :)DISC_1/100)),(2031 - 2003))))+
(D2032 /(POWER((1 + :)DISC_1/100)),(2032 - 2003))))+
(D2033 /(POWER((1 + :)DISC_1/100)),(2033 - 2003))))+
(D2034 /(POWER((1 + :)DISC_1/100)),(2034 - 2003))))+
(D2035 /(POWER((1 + :)DISC_1/100)),(2035 - 2003))))

from DEMAND
where
NETWORK = :CAT_1
and SERVICE_CATCHMENT = :SER_1

This will calculate the NPD value where Y2 = '2035'. If Y2 was '2034' then I would remove the last row which refers to '2035'. So each of the 33 queries is just a subset of this one (except 2036 which is this one plus a line).

So to refer to my last post I was trying to do something like;

IF Y2 = '2004'

THEN select
(D2004 /(POWER((1 + :)DISC_1/100)),(2004 - 2003))))
from DEMAND
where
NETWORK = :CAT_1
and SERVICE_CATCHMENT = :SER_1

IF Y2 = '2005'
THEN SELECT
(D2004 /(POWER((1 + :)DISC_1/100)),(2004 - 2003))))+
(D2005 /(POWER((1 + :)DISC_1/100)),(2005 - 2003))))
from DEMAND
where
NETWORK = :CAT_1
and SERVICE_CATCHMENT = :SER_1

etc


I tried using CASE with a little success but I think it got too big with all of those queries :(
 
Ok, I think I see what you're doing here. And you're right, that would be a lot of CASE statements! Were you doing it like this:

Code:
select
(D2004 /(POWER((1 + (:DISC_1/100)),(2004 - 2003))))+
case Year2 when > 2004 then
(D2005 /(POWER((1 + (:DISC_1/100)),(2005 - 2003))))
else 0 end
+
case Year2 when > 2005 then
(D2006 /(POWER((1 + (:DISC_1/100)),(2006 - 2003))))
else 0 end

If you don't want to use CASE, then the way I would tackle this (in MSSQL) would be a stored procedure using 'Dynamic SQL'. I don't know how this works in Oracle, or if it does, but basically what you do is write a procedure that builds a query, in this case by looping through years between year1 and year2, and adding
Code:
+
'(D' + [Year2Param] + '/(POWER((1 + (:DISC_1/100)),(' + [Year2Param] + ' - 2003))))'

For each extra year.

As I said, I don't know Oracle so I don't konw if this is an option for you (but its' probably worth looking ito). You might like to try the Oracle forum: forum1177

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
mj -
As Alex suggests, a stored function and recursive calls will make your life a lot easier on this one.
Here is a sample function that may not be exactly what you want, but should get you close (or at least point the way):
Code:
CREATE OR REPLACE FUNCTION npd(p_year IN NUMBER, p_base IN NUMBER, p_disc1 IN NUMBER) RETURN NUMBER IS
-- PURPOSE: CALCULATE THE NPD BETWEEN TWO SPECIFIED YEARS.
-- NOTE: THIS FUNCTION ASSUMES THERE IS ANOTHER FUNCTION NAMED "demand" THAT 
--       PROVIDES VALUES FOR D2004, etc, AS STATED IN THE PROBLEM.

   l_npd  NUMBER := 0;
BEGIN
   IF (p_year > p_base) THEN
      l_npd := demand(p_year)/power(1+(p_disc1/100),(p_year - p_base)) 
                + npd(p_year - 1,
                      p_base,
                      p_disc1);
   ELSE
      l_npd := 0;
   END IF;
   RETURN l_npd;
END;
/
Now your query becomes much simpler:
Code:
SELECT npd(2005,2003,:disc_1) 
  FROM demand 
 WHERE network = :cat_1
   AND service_catchment = :ser_1;
Let us know if this works for you.
 
Wow thanks!! I'll take a little time to digest this and come back with some questions!!!

Many many thanks to you guys this is great
 
Hey just to let you know I got around this problem in a very blunt way by creating a table in my Oracle application to mimic the 33 seperate queries needed. It ain't pretty but its pretty robust.

I will roll out this application soon using my less than perfect solution and when my plate clears a little have another go in a test environment at making it a bit smoother.

Many thanks again to you guys who responded. I am amazed at the level of advice you offered. I'll probably revive this topic when I get to round 2.

Cheers and thanks, Michael
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top