1212306 - Null Records are not shown when Record Selection is done on a Child Table

Creating a new report using the native drivers to connect to Microsoft SQL Server where two tables are joined using a left outer join, refreshing the report will only return records that have matching values in the record selection.

Viewing the fields displayed you can confirm that the fields that are equal are displayed but the null values from the child table are not being displayed using the left outer join.

NOTE:=====

To view the join type being used in a report select "Database", "Visual Linking Expert" and right click on the join line. You will have "Options" available from the shortcut menu which will display the join type.

===========

When record selection based on a field in the child table is used in conjunction with an outer join, CR will only return records that fit the selection criteria. For example, when adding selection criteria based on the "Country" field in the child table where "Country" is equal to "USA", CR will filter out Null records, since "Null" is not the same as "USA". Therefore, record selection based on the field in the child table overrides the additional records returned from a left outer join (compared to an equal join).

The following is a list of five major SQL type database and how each database handles null records using the correct SQL Syntax in each database client SQL utility. This indicates that there is no industry standard for this behavior so CR arbitrarily decided to filter null values from the parent table.

Oracle

· filters out null records

DB2

· filters out null records

MS SQL Server

· does not filter out null records

Sybase

· does not filter out null records

Informix

· does not filter out null records

==========

FOR MORE INFORMATION

Search for the related technical brief, Table Linking in Crystal Reports, using the file name, scr_pclinking.pdf, on our support site at http://support.crystaldecisions.com/docs.

==========

CHILD TABLE LEFT OUTER JOIN RECORD SELECTION WHERE CLAUSE Seagate Crystal Reports Microsoft SQL Server, Oracle, DB2, Informix, Sybase StarEquals Null Records and Record Selection , c2007149 , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem