Re: Select on Outer Join field failing the parent record

"David Berg" <dbergquist_at_bd.com> schrieb im Newsbeitrag news:1274998b.0302191828.5f49018a_at_posting.google.com...
> This works perfect within SQL Plus, but i'm using Crystal Reports and
> it knows nothing of the (+)= operator for specifying a value like

you are using (+) in WHERE TABLE1.ID1=TABLE2.ID1 (+) why can't you use it in TYPE (+) = 'size' or if you like more 'size' = TYPE (+) ?

> this. I can't hard code 'size' into the view...needs to be flexible
> for a number of differentn TYPES. So from what i can tell, i would
> need to be able to inner join to a view with TABLE1.ID1 to view field
> MYVIEW.ID1. I would then select on MYVIEW.TYPE = 'size'. Must always
> get back a record for the TABLE11.ID1 even if the 'size' comparison
> fails.
>
> Is there some code to add to view so that when i specify TYPE and it
> doesn't exist, that the ID1 will always exist and thus return a row.

The cannot know anything about where conditions that you are using when you are selecting from that view

>
> create or replace view MYVIEW
> AS
> Select table1.ID1, FRUIT, TYPE, VALUE
> From TABLE1, TABLE2
> Where TABLE1.ID1=TABLE2.ID1 (+)
>
> Usage would be....
>
> select TABLE1.ID1, FRUIT, TYPE, VALUE
> From TABLE1, MYVIEW
> WHERE TABLE1.ID1=TABLE2.ID1 (+) AND
> TYPE = 'size'

try:
NVL(TYPE, 'size') = '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'
> >
Received on Fri Feb 21 2003 - 05:05:42 CST