Filter not workin correctly

[jello212]

line in question:

ID.....chgDt.....chgVdDt.....pmtDt.....PmtVdDt
48781..12/29/06..............1/17/07..........

Filter:
ChgDt = {?dtRng} or
ChgVdDt = {?dtRng} or
PmtDt = {?dtRng} or
PmtVdDt = {?dtRng}

{?dtRng} = 1/1/07 - 1/31/07

According to the logic, the row should be selected but it's not. Any ideas?

 

[lbass]

What is your full record selection formula?

-LB

 

[jello212]

That is the full record selection formula

 

[lbass]

Humor me and please paste the actual record selection formula into the post. It might also help if you go to database->show SQL Query and copy and paste that in here, too.

-LB

 

[jello212]

{vwGenVouchInfo.Posting_Date} = {?Dt Range} or
{vwGenVouchInfo.Date_Voided} = {?Dt Range} or
{vwGenSvcPmtInfo.Posting_Date} = {?Dt Range} or
{vwGenSvcPmtInfo.Date_Voided} = {?Dt Range}



-------------------------------------
SELECT *
FROM (vwGenVouchInfo
INNER JOIN
vwGenSvcInfo ON vwGenVouchInfo.Voucher_ID
=vwGenSvcInfo.Voucher_ID) LEFT OUTER JOIN
vwGenSvcPmtInfo ON vwGenSvcInfo.Service_ID
=vwGenSvcPmtInfo.Service_ID
WHERE (
(vwGenVouchInfo.Posting_Date >=
{ts '2007-01-01 00:00:00'} AND
vwGenVouchInfo.Posting_Date <
{ts '2007-01-31 00:00:01'})
OR (vwGenVouchInfo.Date_Voided >=
{ts '2007-01-01 00:00:00'} AND
vwGenVouchInfo.Date_Voided <
{ts '2007-01-31 00:00:01'})
OR (vwGenSvcPmtInfo.Posting_Date >=
{ts '2007-01-01 00:00:00'} AND
vwGenSvcPmtInfo.Posting_Date <
{ts '2007-01-31 00:00:01'})
OR (vwGenSvcPmtInfo.Date_Voided >=
{ts '2007-01-01 00:00:00'} AND
vwGenSvcPmtInfo.Date_Voided <
{ts '2007-01-31 00:00:01'})
)
ORDER BY vwGenSvcInfo.Service_ID

 

[lbass]

You are making selections on a left outer joined table in the where clause--this will in essence undo the left outer join. As a test, remove the last two clauses in the record selection formula that reference the vwGenSvcPmtInfo table. Do the records then appear?

What version of CR are you using? Do you have the option of using a command as your datasource?

-LB

 

[jello212]

No, I'm able to copy and paste the sql statement into MS Query Analyzer and it runs fine.

I think I have it figured out. The problem is with the 2nd line of the section.

ID.....chgDt.....chgVdDt.....pmtDt.....PmtVdDt
48781..12/29/06..............1/17/07..........

Filter:
ChgDt = {?dtRng} or
ChgVdDt = {?dtRng} or
PmtDt = {?dtRng} or
PmtVdDt = {?dtRng}

It's choking on the null value of the charge void date. Once crystal encounters a null value on the record, it stops checking that record and moves onto the next record. It never tests to see if the payment date on that line qualified or not. I am able to get around it by creating 2 different flags: chg flg and pmt flg. These flags account for the null value. The record selection now says: chg flag = 1 or pmt flag = 1

 

[lbass]

Sorry, I should have realized that was the issue. Not sure how the flags are working. You could use:

(
(
not isnull(ChgDt) and
ChgDt = {?dtRng}
) or
(
not isnull(ChgVdDt) and
ChgVdDt = {?dtRng}
) or
(
not isnull(PmtDt) and
PmtDt = {?dtRng}
) or
(
not isnull(PmtVdDt) and
PmtVdDt = {?dtRng}
)
)

Your left join is still potentially being compromised however.

-LB

 

[beltmanjr]

LB is correct.
A filter on the right table of your left outer join will reduce it to a normal join. The only way to fix this is to transform the where clause into the ON clause.

 

[jello212]

Wouldn't the fact that all of the filters are connected with "OR" instead of "AND" keep the left join in tact?

Also, I'd love to be able to establish constraints as part of the ON clause. How do I do that in Crystal?

 

[beltmanjr]

As far as I'm aware you can only get this done using a command statement which is something that really sucks about Crystal.

The OR statement still indicates a Where clause on the right half of the left outer join and therefore breaks it. Even tricks with OR ISNULL(bla) may not work.

However, looking at one of my own reports it seems to work if you make it something like:

isnull(ChgDt) OR
ChgDt = {?dtRng}

It's important to allow for the ISNULL and it being the first part of the statement.

 

[dgillz]

The workaround for the conditions on the right side table is to conditionally suppress the unwanted records rather than try to filter them out with a record selection formula.

Of course, suppressed records will still evaluate in any group or grand totals you have, so you will have to use running total fields or variables to accomplish this.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[jello212]

I've verified the #s and the filter is respecting the left outer.

Is there a way to get the command statement to respect the indexes on a table/view? Every time I use a command statement, performance goes to crap.

 

[beltmanjr]

yeah I know. If you link a command to anything it will collect all data before filtering rather than honouring your sql.

So you put everything into a command or dont use it at all.