Hi,

I have a report which has a drill-down report. Some times we'll get an empty value in drill column. If we click on empty value for drill report, I'll add the

'TOTAL' 

in WHERE clause like below.

WHERE TOTAL <Column_Name> EQ ' ' 

Without 'TOTAL' in the WHERE clause, the report getting 0 records for empty value.

While using 'TOTAL', the report reading the entire base and then retrieves the Empty records correctly. So the report taking a long time to retrieve output for Empty value.

Is there any other way to pull empty records? or how to compare/filter empty value in WHERE clause?

I tried the MISSING=ON but it's not working in my report.

I'm using AP version 8105m.

Thanks.

This message has been edited. Last edited by: FP Mod Chuck,

WHERE COLUMN EQ MISSING

Hi Frans,

thanks for your reply. I already tried your code but it's not working as expected.

Maran

Your WHERE statement has a blank space between the tick marks. Try this

WHERE TOTAL Column_Name EQ ''

Chuck,

If I use TOTAL in WHERE, I'm getting the report correctly. But it takes long time to run. That is the issue.

Maran

How many records is it processing?

Chuck,
It's processing around 150K records. If we use TOTAL in WHERE clause, it processing entire base and then retrieves Empty value records. That's why it takes time.

Maybe this is one of those queries you either need run deferred or schedule on whatever basis is required.

First you need to know how the data is stored on the DB. Is it null, 0, '' or ' ' to avoud using TOTAL you can try to use DB_EXPR to use some native SQL to filter this data.

quote:

Is it null, 0, '' or ' ' to avoud using TOTAL you can try to use DB_EXPR

Frans,

thanks for your reply. I'll check on DB_EXPR.