-
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.
Here is how the data looks -
attribute_id, work_id, res_id, attr_start_date, attr_finish_date, attr_amount
1 1 2 10/10/01 10/18/01 10
2 2 3 10/13/01 10/15/01 20
3 3 3 10/09/01 10/20/01 11
4 3 2 10/10/01 10/20/01 16
I want to create a view which will give me results like this...
I will just do it for 1 st row.
attribute_id, work_id, res_id, date , amount
1 1 2 10/10/01 10
1 1 2 10/11/01 10
1 1 2 10/12/01 10
1 1 2 10/13/01 10
1 1 2 10/14/01 10
1 1 2 10/15/01 10
1 1 2 10/16/01 10
1 1 2 10/17/01 10
1 1 2 10/18/01 10
So in the view I want to see the key columns and amount repeated for date between attr_start_date and attr_finish_date
(inclusive).
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 ?
Thanks
Sonali
Sonali
-
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 :
select attr_id,work_id,res_id,
attr_start_date+i curr_date,attr_amount
from attr a,xyz b
where attr_start_date+i<=attr_finish_date
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...)
svk
-
Thanks a lot, the idea worked..
Sonali
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|