I have a table attribute which has following columns
attribute_id, work_id, res_id, attr_start_date, attr_finish_date, attr_amount
where attribute_id is Primary key of this table and work_id, res_id are FKs and work_id, res_id makes a unique key.
There are many other columns but I am keeping it simple here.
So in the view I want to see the key columns and amount repeated for date between attr_start_date and attr_finish_date
How do I write this view.. I know this can be done with stored procedure with while loop and/or cursor. But I am interested in doing this as a view rather than PL?SQL block. IS this possible in a view ?
We use Oracle 8.1.7 here.
Can you have a cursor or while loop in Case statement or in creating a view ?
Is this possible by doing joins/ subquery ?
12-15-2001, 03:58 AM
Hey, how serious are you @ this ?
How much is going to be the difference between the start_date and finish_date values in your attr table records ? If not too much, read this :
Create a table (say XYZ) with a numeric column (say I). Assuming that the difference in the dates will not be more than 30 days, put 30 rows in this XYZ tables with values 1 to 30.
And then you are all set to fire the following query to get the required output :
from attr a,xyz b
order by 1,2,3,4;
(Don't laugh...just :), I'm sure someone can post a better solution or even you yourself can engineer one. And I remember a similar issue having gotten discussed before in this forum...)