Select table1.ID1, FRUIT, TYPE, VALUE
From TABLE1, TABLE2
Where TABLE1.ID1=TABLE2.ID1 (+)
and TABLE2.TYPE(+) ='size'
"David Berg" <dbergquist_at_bd.com> schrieb im Newsbeitrag
news:1274998b.0302141811.9ffcd66_at_posting.google.com...
> 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 - 11:05:11 CST
