How to make Crystal using ANSI outer joins?
Posted on 2009-12-22
I'm working on some reports with Crystal in conjunction with a Sybase Database server.
I realized that Crystal generates the Outer Joins using the crystal specific syntax ( *= instead of LEFT OUTER JOIN). With this it is not possible to filter on the values of the second table which is added by the outer join.
Using the ANSI syntax with table1 OUTER JOIN table2 on table1.field = table2.field solves this problem when I use a direct sql query.
Is there a possibility to setup crystal in a way that it uses the ANSI syntax instead of the proprietary sybase syntax?
From table1 LEFT OUTER JOIN table2 on table1.UserID = table2.UserID
WHERE table2.location = 'Test'
will only return the entries that have 'Test' in the field of the second table.
From table1, table2
WHERE table1.UserID *= table2.UserID AND
table2.location = 'Test'
will return all entries for the table1 because the filter on the second table is ignored by sybase.
Any help will be appreciated.