Cr7: IsNull defeats server-side processing

[KPharm]

I'm using VB6 with Crystal 7 and the following segment from our WHERE clause that we pass into the report through the Automation Server (cpeaut32.dll) is making Crystal bring back ALL of the records instead of just the ones we need:

sCriteria = "{ISSUES.ISSUE_STATE} = 'Q' and IsNull({ISSUES.NUMBER_ID}) = True"

We need the ISSUES.NUMBER_ID's that are NULL and they are Number fields. The above brings back thousands of records instead of the two records that we need. Is there another sure-fire way to check for NULL without using Crystal as the filtering device, thus leaving it up to Oracle to speed things up? Crystal doesn't understand normal NULL syntax, such as:

sCriteria = "{ISSUES.ISSUE_STATE} = 'Q' and {ISSUES.NUMBER_ID} is not NULL"

Thanks

 

[rhinok]

Can you base the report on an Oracle view that has the syntax:

ISSUES.ISSUE_STATE = 'Q' and
ISSUES.NUMBER_ID} is not NULL

in the Where clause of the view?

 

[KPharm]

I wish I could but people here get very edgy when we propose new views and tables, especially since everything goes through formal testing and they're always backed up. The changes that I'm making now have to be done to existing code so it's not as a big deal.

Thanks for replying - any more takers?

 

[darwin101]

you could use a query to do all the processing on oracle , or i ran into a similar issue where CR would not send over the null code in its version of SQL, i had to hard code it into CR's SQL. that worked.