-
dynamic sql
I am new oracle programming and I want to create a dynamic view , which is a combination of following tables –
Project Table
Project F code
DSAA 0012
DSAB 0013
DSAC 0013
Activity Table
Activity Activity Value
1000TA 12
2000TA 13
3000TA 14
Activity Def Table
Owner Activity name
DS ASD
DS AFD
DS PFD
DO ASD
DO AFD
DO PFD
Right now this table has 51 records. In future there chance it might increase to infinite number of rows. Each row I this Table has related value in the Activity Table. I want dynamic code in the View so that when ever this table is updated it should reflect in the results.
This how the Result should look like-
Project Activity ASD AFD PFD Activity ASD AFD PFD
DSAA 1000TA 12 12 12 2000TA 13 13 13
DSAB 1000TA 12 12 12 2000TA 13 13 13
I want the values to be displayed as a cross tab. The rows in the Activity Def and Activity Tables should be displayed as columns in the Result. Each Activity Name in the Activity Table will have a record in the Activity Table.
Thank You Very Much,
gopi
-
relationships
Can you explain the relationships between the three tables?
David Knight
OCP DBA 8i, 9i, 10g
-
Hi David,
The Activity Def Table will have more Owner and Activity Name records in future. Right now it has 51.Whenever a new record is updated in the Activity Def Table the code has to look for the corresponding Activity Value in Activity Value.
Activity Table
Activity ASD AFD PFD
1000TA 12 12 12
2000TA 13 13 13
3000TA 14 14 14
This is how the Activity Def Table will look -
Owner Activity name
DS ASD
DS AFD
DS PFD
DO ASD
DO AFD
DO PFD
PS ASD
PS AFD
PS PFD
DK ASD
DK AFD
DK PFD and so on.
The Project Info is coming from Projects Table,
Activity and ASD,AFD,PFD values are coiming from Activity Table,
ASD,AFD,PFD clomns are coming from Activity Def Table.Right now Activity Def table has 51 records. so 51 column names will appear in the results -
Proj Activity ASD AFD PFD Activity ASD AFD PFD Activity ASD AFD PFD
DSAA 1000TA 12 12 12 2000TA 13 13 13 3000TA 14 14 14
DSAB 1000TA 12 12 12 2000TA 13 13 13 3000TA 14 14 14
So on for 51 Activities for one particular Project.
Thank You very much,
gopi
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
|