Outer Join Problem: Please Help!

[CrystalVisualBOracle]

Hello All. I have a report which queries off of two Oracle 8 tables. The tables are linked with an outer join.

SELECT
LICENSE_DATA.KEY,
LICENSE_DATA.DESCRIPTION,
USER_RIGHTS.USERID,
USER_RIGHTS.STARTED,
USER_RIGHTS.EXPIRED
FROM
LICENSE_DATA,
USER_RIGHTS
WHERE
LICENSE_DATA.KEY = USER_RIGHTS.KEY (+) AND
LICENSE_DATA.KEY = (?Param_Key) AND
USER_RIGHTS.STARTED < (?Param_Date) AND
USER_RIGHTS.EXPIRED >= (?Param_Date)

The problem I'm having is with the last two lines. When they are added, the query no longer returns as an outer join. In order to correct this those lines should read as..

USER_RIGHTS.STARTED (+) < (?Param_Date) AND
USER_RIGHTS.EXPIRED (+) >= (?Param_Date)

Unfortunately, Crystal doesn't seem to support this syntax. I get a DLL error. The query works if I run it in Oracle directly just not if I run it in Crystal. Does anyone have any advice for a work around? Any help would be great!

Thanks in advance,
CrystalVisualBOracle

 

[todder]

Did you enter the last three lines of the where clause in the &quot;Select Expert&quot; in crystal reports or are you coding the whole query in the &quot;Show SQL Window&quot;? What version of crystal?

 

[CrystalVisualBOracle]

Hey, thanks for responding. Originally, I modified those lines using the &quot;Select Expert&quot; window but when I had to try and add out joins I modified the Show SQL Window. I'm using Crystal 8.

 

[kenhamady]

When you put criteria on the outer table you nullify the outer join. It can't be both Null and between the dates. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Ngolem]

Also I have found that when you outter join to a table but place selection criteria on a field that is not linked to the parent table...then when that field value does not exist it kills they whole record that you would have normally brought back.

My only solution to this problem was to remove the selection criteria from the Record selection and use a formula to select the records I wanted during the printing.

This isn't a good solution that gets worse as time goes on since you bring back many records you don't need.

My ultimate solution (if I ever get them to use 8.5 developer) is to create a record set using a proper query.

 

[CrystalVisualBOracle]

Yeah that's exactly what I ended up doing. Why a query will work in the Crystal SQL Designer but not in Crystal Reports is beyond me. But that's what I had to do. Thanks.

CrystalVisualBOracle