| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Select on Outer Join field failing the parent record
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
------------------------
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"
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 Fri Feb 14 2003 - 20:11:29 CST
 |
 |