Home » SQL & PL/SQL » SQL & PL/SQL » Using PL/SQL to generate List to match Records
Using PL/SQL to generate List to match Records [message #260738] Mon, 20 August 2007 16:55 Go to next message
hdogg
Messages: 93
Registered: March 2007
Member
I am new to stored procedures and have done tons of research trying to figure out how i can use them...The concept isn't quite coming all together....

What I am trying to do.....


Use a loop to generate the next 24 month end
31-AUG-07
30-SEP-07
31-OCT-07 etc


i select the latest_date from the actuals table
select max(date)as latest_date from the Actuals table where rownum <2.


Then i would loop the next 24 months

    for var in 0 ..24 loop 
         add_months(latest_date, var);
    end loop;
end



After the loop is generated... I would like to be able query a forecast table and essentially union the dates to the forecast d
SELECT AMOUNT, FUTURE_DATE FROM FORECASTS WHERE FUTURE_DATE = ((date(s) from stored procedure))


Would stored procedures be the way to go for this?
Re: Using PL/SQL to generate List to match Records [message #260767 is a reply to message #260738] Mon, 20 August 2007 21:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Would stored procedures be the way to go for this?
Never use PL/SQL, when SQL can solve the problem.
Re: Using PL/SQL to generate List to match Records [message #260769 is a reply to message #260738] Mon, 20 August 2007 21:38 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
In addition, how to generate rows in SQL is described in Oracle Row Generator Techniques article.
Its usage for generating dates (although different from your case) is discussed in Creating a Calendar (merged 3 topics) thread.
Re: Using PL/SQL to generate List to match Records [message #261032 is a reply to message #260769] Tue, 21 August 2007 09:48 Go to previous messageGo to next message
hdogg
Messages: 93
Registered: March 2007
Member
flyboy, michel --

Thanks a bunch for pointing me the right direction... Smile The oracle row generator techniques showed me the right way to get a list of 24 months....



Michel: Why do you say stay away from PL/SQL whenever possible???


Here's what I did:


(period is 31-JUL-07)
SELECT add_months((SELECT max(to_date(period, 'mm/dd/rr')) AS PERIOD FROM FORECAST_DATA ),+ROWNUM) FORECASTMONTH
FROM   ( SELECT 1 just_a_column
         FROM dual
         CONNECT BY LEVEL <= 24
       )
WHERE  ROWNUM <= 24


that yields
31-AUG-07
30-SEP-07

etc

Is there any redundancy in this?

-HDogg
Re: Using PL/SQL to generate List to match Records [message #261047 is a reply to message #261032] Tue, 21 August 2007 10:45 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Why do you say stay away from PL/SQL whenever possible???

Because SQL is more efficient than PL/SQL.
Quote:
Is there any redundancy in this?

Yes inner query generates only 24 rows so you don't have to limit the number of rows in outer query.
But this is not the good way to do it as you will (logically) reexecute the query in select 24 times.
It is better to use something like:
with
  data as ( SELECT max(to_date(period, 'mm/dd/rr')) AS PERIOD FROM FORECAST_DATA ),
  lines as ( SELECT 1 just_a_column FROM dual CONNECT BY LEVEL <= 24 )
select add_months(period,ROWNUM) FORECASTMONTH
from data, lines
/

Regards
Michel
Previous Topic: create Sequences Base 16
Next Topic: syntax error
Goto Forum:
  


Current Time: Sat Dec 10 18:28:12 CST 2016

Total time taken to generate the page: 0.15401 seconds