Re: Select on Outer Join field failing the parent record

This works perfect within SQL Plus, but i'm using Crystal Reports and it knows nothing of the (+)= operator for specifying a value like 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.

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'

"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 Wed Feb 19 2003 - 20:28:44 CST