| PL/SQL function in FROM clause [message #19797] |
Wed, 10 April 2002 08:46  |
Doug
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
We have a set of tables that are rebuilt every year. For example: XY_2000, XY_2001, XY_2002, you get the idea. The table stores that year's information. (Note: We did NOT come up with scheme! we just have to live with it.)
When we query these tables we have to use the current year's name, ex: XY_2001. When the tables are rebuilt for the change of the year all of our queries have to be updated.
My idea:
Write a function that we can pass in the non-changing portion of the table name and we can then calculate the year we want and return the table name. We could use the function in the from clause (SELECT * FROM year_table('XY')). Doing so would prevent yearly maintenance of our queries.
I have tried a number of different methods to accomplish my idea but all have resulted in failure. Does anyone know how to successfully implement my idea?
Thank you for the help!
|
|
|
|
| Re: PL/SQL function in FROM clause [message #19801 is a reply to message #19797] |
Wed, 10 April 2002 10:08  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Unfortunately, your idea of creating a function will not work because you need an actual object for the table name unless you are using dynamic SQL.
So, since you have all these dependent queries, how about creating a view to handle this?
The view would have to be updated once per year, but all of your queries would use the view and would not have to change each year.
create or replace view xy
as
select * from xy_2002;
All of your queries would then refer to xy.
|
|
|
|
| Re: PL/SQL function in FROM clause [message #19817 is a reply to message #19797] |
Wed, 10 April 2002 23:54 |
Epe
Messages: 99
Registered: March 2002
|
Member |
|
|
Hello,
if possible, I would modify the tables and selects to XY (without year suffix), and at the end of every year, rebuild the tables with the name XY_year and move or copy the data into those "history"-tables.
So the data from the active year are stored in the tables without year suffix, and moved or copied at the end of the year to the table with the correct suffix.
That way you will have to modify your selects only this year (to XY).
Success,
epe
|
|
|
|
| Re: PL/SQL function in FROM clause [message #19821 is a reply to message #19817] |
Thu, 11 April 2002 05:08 |
Doug
Messages: 7
Registered: May 2001
|
Junior Member |
|
|
If I could I would change the schema but alas I cannot, I am only supporting someone's design. But, that is a rant for another day.
I am not averse to using dynamic sql or pl/sql. I would like the solution to be as simple as possible for maintenance considerations.
Doug
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
