Changing Outer Join Syntax

[kenhamady]

Ran into a problem using v8.5 with SQL Server 2000 and the direct driver (not ODBC). We are joining two tables, Admissions and Terminations. An active Patient has no termination record. To get patients active in a specific period we use an outer join from Admissions to Terminations and the following Selection formula:

IsNull ({table.TermDate}) or {table.TermDate} > {?End Date}

This generate SQL that looks logically correct, but uses the proprietary *= syntax for the LO Join. This performs the join after the filter. End result is that ALL patients show up in the report, even those with a term date before the end date.

If I let Crystal do the filtering locally I can get the result I want - albeit slowly. If I do it through ODBC I get the ANSI style LO Join syntax which works the way I want, but ODBC is not a good option in this project. We are trying to avoid manually modifying the SQL in these reports so that the users can maintain them more easily.

Ideally I would like to find a way to have CR generate ANSI standard SQL through the SQL Server native client. Just haven't found a way to make it happen. Anyone know of a way? I have seen that CR has a registry key that allows you to specify the LO Join syntax for certain ODBC drivers, but I don't believe that this would work for the native client.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[cmmrfrds]

Would Microsoft OLEDB provider for sql server work for you? It is interesting that the old style syntax is generated since Microsoft encourages using the ANSI 92 standard join syntax for sql server 2000.

 

[andyskinner]

I think it creates the old Star Equals syntax for backward compatibility.....

What have you set in the link options between the 2 tables Ken? Is it just Left Outer Join?

Using ODBC shouldn`t force you to manually modify the SQL either? If your solution has to scale, using Native drivers would cause you some serious problems down the line.

Andy.

 

[kenhamady]

It appears that this is a Crystal v8.5 issue. If I open the same report in v9 it generates ANSI standard syntax - giving us the behavior that we want.

The interesting thing is that v9 is not supposed to support the SQL Server native client (only ODBC or OLEDB). What that appears to mean is that you won't see the native connection when creating a new report or when setting the location of an existing report. However, if you have an existing report from an older version and open it in v9 it will run against the native connection just fine, as long as you don't mind the change in the SQL Syntax.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com