Re: Select on Outer Join field failing the parent record

Here is the ANSI/ISO way:

select
 t1.id1,
 t1.fruit,
 CASE WHEN t2.thetype is null THEN 'size'

    ELSE t2.THETYPE
    END as thetype,
 t2.value
from t1 left outer join t2 on t1.ID1 = t2.ID1 and t2.THETYPE = 'size' order by 1;

The case stuff just sticks in the word size

"Gernot Ficker" <gernot_ficker_at_hotmail.com> wrote in message news:b2lrvp$dc5$07$1_at_news.t-online.com...
> 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 Sun Feb 16 2003 - 12:16:11 CST