Crystal 8.5 not returning the same records as the query in SQL 1

[PTW]

I have created a simple report in Crystal Reports 8.5 that joins 2 tables, returning 1315 records. When I add a third table as an outer join, Crystal then only returns 58 records.

I thought it might be a problem of not finding matching records in that 3rd table, but when I copy the SQL from Crystal into MS SQL's Query Analyzer, it works fine and returns all 1315 records.

Any idea where I might be going wrong in Crystal? I am very frustrated by what appears to be Crystal Reports' inconsistent behavior, and this report that took me 10 minutes to write in SQL has taken me all week in Crystal and is still not working. Thanks. (and sorry for the rant)

 

[lbass]

You should be using a left outer FROM one of your original tables to the third table. If that doesn't help, please specifically identify the tables and how you have linked them. Also note that you cannot have a selection on the right most table and still maintain the left join.

-LB

 

[kskid]

Click on Database->Show SQL Query and compare it against your SQL.

By any chance, do you have a selection option on the 3rd table other than an outer join? If so, Crystal will treat it as an equality.

Change you record selection on table3 as follows

(
isnull(table3.field) or {table3.field} = "value"
)

-LW

 

[PTW]

Thanks for your replies.

There are three tables: eeEmployee, eePayStatus, and miscPosition.

eeEmployee and eePayStatus are linked by the eeLink field.
eeEmployee and miscPosition are linked by the idPosition field.

Each table maintains an audit history, so there is a potential for many-to-many relationships. To avoid that, my report has a parameter for a date (in this case, I used Nov 1st, 2004). I only want to select records from each table where (CreatedDate < parameter date) AND (EndDate > parameter date OR EndDate is null).

Here is the SQL as created by Crystal. Crystal returns 58 rows. Copied to SQL Query analyzer, this SQL script returns the expected 1315 records.

I have just noticed that the records returned by Crystal are where the miscPosition.EndDate field is -not- null, and leaves off all the ones that -are- null. I find this puzzling since the join is on idPosition, not EndDate.

SELECT *
FROM
{ oj ("eeEmployee" eeEmployee INNER JOIN "miscPosition" miscPosition ON
eeEmployee."idPosition" = miscPosition."idPosition")
INNER JOIN "eePayStatus" eePayStatus ON
eeEmployee."eeLink" = eePayStatus."eeLink"}
WHERE
eeEmployee."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
(eeEmployee."EndDate" >= {ts '2004-11-01 00:00:00.00'} OR
eeEmployee."EndDate" IS NULL) AND
eePayStatus."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
(eePayStatus."EndDate" >= {ts '2004-11-01 00:00:00.00'} OR
eePayStatus."EndDate" IS NULL) AND
miscPosition."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
(miscPosition."EndDate" >= {ts '2004-11-01 00:00:00.00'} OR
miscPosition."EndDate" IS NULL) AND
eePayStatus."idPayStatus" = 'A'
ORDER BY
eePayStatus."idProvState" ASC,
eePayStatus."ZCode" ASC,
eeEmployee."ChequeName" ASC

 

[PTW]

Doh--sorry about my opening post. I made a mistake--the tables are actually inner joins, not outer joins. I guess I am confused by that "oj" thing that Crystal adds.

 

[kskid]

I take it that miscPosition is the 3rd table. Then try this

(
isnull(miscPosition.id) or
(
 miscPosition."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
    (miscPosition."EndDate" >= {ts '2004-11-01 00:00:00.00'} OR
    miscPosition."EndDate" IS NULL)
)
)

-LW

 

[lbass]

If MiscPosition is the third table, try a left join from
EE Employee to MIscPosition and change the order of the clauses to:

miscPosition."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
(miscPosition."EndDate" IS NULL or
miscPosition."EndDate" >= {ts '2004-11-01 00:00:00.00'})

You realize that your statement requires a record to meet the criteria for every table, right?

-LB

 

[kskid]

Missed a set of parenthesis

(
isnull(miscPosition.id) or
(
 miscPosition."CreatedDate" < {ts '2004-11-01 00:00:00.00'} AND
   (
    (miscPosition."EndDate" >= {ts '2004-11-01 00:00:00.00'} OR
    miscPosition."EndDate" IS NULL)
   )
)
)

The isnull(miscPosition.id) handles the outerjoin if there are no records found on the 3rd table. The remainder check for valid date if a matching record is found.

-LW

 

[PTW]

Thanks for your help everyone.

The third table (miscPosition) does have a matching record for every idPosition value in eeEmployee.

The problem seems to be Crystal being fussy about nulls in the Record Selection Formula. If the miscPosition record had a null in the EndDate field, the join failed.

So the following does not work:

({miscPosition.CreatedDate}<{?GetDate}
and ({miscPosition.EndDate}>={?GetDate} or isnull({miscPosition.EndDate})))


Instead, I created a formula field:

if isnull({miscPosition.EndDate} ) then
CurrentDate
else
{miscPosition.EndDate}


and then I put this into the Record Selection Formula:

({miscPosition.CreatedDate}<{?GetDate}
and {@NewPositionEndDate}>={?GetDate})



This works. The problem seems to have been having a null in the Record Selection Formula. So if the record has a null value in the EndDate field, the following would fail and the join would not be made:

{miscPosition.EndDate}>={?GetDate}

 

[lbass]

The issue is that nulls have to be tested first. In your first try, you had the null last. The one that worked used the isnull test before establishing other criteria, so the following would also work:

(
isnull({miscPosition.EndDate}) or
{miscPosition.EndDate}>={?GetDate}
) and
{miscPosition.CreatedDate}<{?GetDate}

-LB

 

[PTW]

Thanks lbass. That simple bit of info not only solved my problem, but will save me a lot of time in the future!