Select ISNull and > on date field 1

[BiJae]

I'm building a report that calculates information for a point in time. I'm using a date field as my primary selector in a table listing institutions. The default for this field is NULL. When an institution is added the date field is null and it appears on the list. When a date is entered into that field it is removed from the list. The date is effectively the cancel date for the institution.

My problem appeared when I received an institution that was canceled in the middle of this month. When I ran the report for last month that institution was not listed. However, it should be because it was still active at that point. So I built an additional piece to my report. Here's the select formula I built:

isnull({creditunions.CancelDate}) and {creditunions.CancelDate} > {?SelectDate} and
{build.REPORT} = "RE" and
({build.ACTIVE} = "YES" and
{build.RECDATE} < {?SelectDate}) or
({build.ACTIVE} = "NO" and {build.InactiveDate} in [{?SelectDate} to today()])

This returned 1460 records, far more than the 223 I was expecting. I wrote a query in the MySql database that followed the logic to obtain the correct results (the null values AND the one with a cancel date > a select date). When I executed the query I received the expected results. This lead me to look at what Crystal XI was saying the SQL was. I looked at the SQL query from Crystal and saw the following:

 SELECT DISTINCT `creditunions1`.`CancelDate`, `build1`.`REPORT`, `creditunions1`.`Name`, `build1`.`EXAMDATE`, `build1`.`CURRENTDAYS`, `build1`.`NETWORTH`, `build1`.`COMPOSITE`, `build1`.`FOLLOWUPDATE`, `build1`.`ACTIVE`, `build1`.`LDOSDATE`, `build1`.`SCHEDULEDDATE`, `build1`.`ZONE`, `build1`.`RECDATE`, `build1`.`InactiveDate`
 FROM   `tcud`.`creditunions` `creditunions1` INNER JOIN `tcud`.`build` `build1` ON `creditunions1`.`CUIndex`=`build1`.`CUIndex`
 WHERE  (`creditunions1`.`CancelDate`>{d '2006-07-31'} AND `creditunions1`.`CancelDate` IS  NULL  AND `build1`.`REPORT`='RE' AND `build1`.`ACTIVE`='YES' AND `build1`.`RECDATE`<{d '2006-07-31'} OR `build1`.`ACTIVE`='NO' AND (`build1`.`InactiveDate`>={d '2006-07-31'} AND `build1`.`InactiveDate`<={d '2006-08-28'}))

I copied this code and entered it directly in mysql. The result was far different from what Crystal produced. I received only the records that I was expecting.

How can crystal pass the query to the database and return far too many records and the same query in MySql (copied and pasted) returns the desired results?

Is there something flawed in my select formula? Is there a better way to tell crystal, I want everything with a NULL value AND everything with a date greater than X?

I thank you for your assistance.

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart

 

[lbass]

I think you got records because you didn't set off your "or" statement towards the end, so the report returned the part of the statement after your "or" based on this:

or
(
{build.ACTIVE} = "NO" and
{build.InactiveDate} in [{?SelectDate} to today()]
)

The two clauses of the "or" need to be set off, and also
in the first part of your statement you are requiring the same record be both null AND to have a date > the parameter date. I would think it should be:

(
isnull({creditunions.CancelDate}) or {creditunions.CancelDate} > {?SelectDate}
) and
{build.REPORT} = "RE" and
(
(
{build.ACTIVE} = "YES" and
{build.RECDATE} < {?SelectDate}
) or
(
{build.ACTIVE} = "NO" and
{build.InactiveDate} in {?SelectDate} to currentdate
)
)

-LB

 

[BiJae]

Thank you!

That worked like a charm. I appreciate your helping me out!

http://www.bijae.net

"If the only prayer you said in
your whole life was, 'thank you,'
that would suffice."
-- Meister Eckhart