Re: Select on Outer Join field failing the parent record

> 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 (+)

Crystal reports allows for an outer join on a table but not a (+)= for a value like TYPE . I was able to get a view that link TABLE1.ID1 through a cartesian product lookup table of all possible TABLE2.ID1 and then linked this lookup view to TABLE2.ID and selected on the TYPE in the lookup table since it will always exist.

create or replace view VWLOOKUP
as
Select DISTINCT TABLE1.ID1, TABLEOFALLPOSSIBLETYPES.TYPE from TABLE1, TABLEOFALLPOSSIBLETYPES

Then i used this view within Crystal Reports as below but it is slow, since there are 500 possible characteristics in the Type Definition table..

select TABLE1.ID1, VWLOOKUP.TYPE, TABLE2.VALUE from TABLE1, VWLOOKUP, TABLE2
where TABLE1.ID1 = VWLOOKUP.ID1 and

   VWLOOKUP.ID1 = TABLE2.ID1 (+) and
   VWLOOKUP.TYPE = TABLE2.TYPE (+) and
   VWLOOKUP.TYPE = 'size'

> try:
> NVL(TYPE, 'size') = 'size'
>

Once again the issue here is that i cannot issue oracle specific syntax without severly limiting the usage of Crystal Reports. To do it this way i would have to create dozens of views for each attribute i may want to bring out as a column and hard code 'size' into the view. Received on Fri Feb 21 2003 - 11:35:21 CST