I've done extensive searches on null records in outer join and
different ways to use NVL(), i just can't get it.
What i have is...
Table 1
ID1 FRUIT
--- -------
1 cherries
2 apples
3 apricots
4 bananas
Table 2
ID2 ID1 (FK) TYPE VALUE
---- ---- ----- -----
1 1 color red
2 1 size 1 inch
3 1 brand bing
4 2 color green
5 2 brand golden
6 3 size
------------------------
I am getting...
VIEW_WITH_MISSING_DATA
ID1 FRUIT TYPE VALUE
---- ----- ------ ------
1 cherries size 1 inch
3 apricots size
Select ID1, FRUIT, TYPE, VALUE
From TABLE1, TABLE2
Where TABLE1.ID1=TABLE2.ID1 (+)
and TABLE2.TYPE ="size"
What i want for output is ....
VIEW1
ID1 FRUIT TYPE VALUE
---- ----- ----- ------
1 cherries size 1 inch
2 apples size
3 appricots size
4 bananas size
------------------------
Extended explanation ....
My true preference for output is more like below. I would like to
select 1 or more Types with an IN clause and get back a column for
each type but don't know if it is possible to account for the number
of variations of Types to transform to columns and that this View
would be used in Crystal Reports.....
VIEW2
ID1 FRUIT COLOR SIZE
---- ----- ------ ------
1 cherries red 1 inch
2 apples green
3 appricots
4 bananas
Dozens of different versions of the report are needed using 1-7 of 500
possible Types (displayed as columns) depending upon the
characteristics that are needed to define that category of "fruit".
Any attached characteristic to that fruit can have no Value (see
Table2 record 6). So there are 500 possible Types used throughout
system though only 5-40 will be assigned to that "fruit" and stored in
Table2 of which only 0-7 would need to be listed as columns on any
report.
Whatever View I come up with will be used in conjuction with a Crystal
Report so i believe the field names need to be static for every time
the view is used. For this reason i was thinking that a view that
returned 1 column Type at a time would suffice as i specified in
View1. I would link from the main report tables to view where the
View.Type="DesiredColumn" that i want for that particular report and
then link up to 7 additional times for each column that is needed and
select the 1 Value column i need for view to return for that Type.
I'm a report writer by trade, so i am a little bit out of my territory
when it comes to designing these sorts of views.
Received on Sat Feb 15 2003 - 03:11:29 CET