Symptom
A report is created against a Microsoft SQL Server database through an ODBC connection using the Seagate Crystal Report Designer. Two tables are added to the report and linked using a left outer join.
i.e. TableA -> TableB
Record selection criteria is added to a field in TableB to further filter the data.
Previewing the report only returns records where there is a match between TableA and TableB, giving the appearance of an equal join.
Viewing the SQL query in the report designer through the "Database" menu, "Show SQL Query" option, revealed that the report designer had generated the following query, using ANSI-92 standard join syntax:
select
TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2
from
{ oj TableA LEFT OUTER JOIN TableB ON TableA.Field1 = TableB.Field1 }
where
TableB.Field2 = 'Test'
A left outer join dictates that all records will be returned from TableA, as well as any matching records from TableB. The join should have returned all of the records in TableA, as well as any matching records from TableB where Field2='Test'
Running the same SQL query through Microsoft SQL Server's ISQL_w utility, returned the same records as the Seagate Crystal Report Designer. Microsoft SQL Server's standard join syntax is STAR-EQUAL, but it can also perform joins using ANSI-92 standard join syntax.
Modifying the SQL query in ISQL_w, as follows, to use STAR-EQUAL join syntax returned the correct results:
select
TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2
from
TableA, TableB
where
TableA.Field1 = TableB.Field1 and
TableB.Field2 = 'Test'
According to Microsoft KBase Article Q176480 titled "How to Restrict the Inner Table of an ANSI Outer Join", the correct way to perform record selection on an inner table of an outer join, using ANSI-92 standard join syntax, is in the FROM clause of the SQL query as part of the join. The initial query would have to be modified as follows, in order to return the correct results:
select
TableA.Field1, TableA.Field2, TableB.Field1, TableB.Field2
from
{ oj TableA LEFT OUTER JOIN TableB ON TableA.Field1 = TableB.Field1 and
TableB.Field2 = 'Test' }
Read more...
Product
Keywords
ODBC ANSI-92 LEFT OUTER JOIN SYNTAX Seagate Crystal Reports Connecting via ODBC MS SQL Server Incorrect Join/SQL Syntax , c2003310 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.