Crystal Report: IsNull equivalent for SQL Expression

0
votes

I want to filter out the data in my crystal report. Currently, I have the following formula in the Record Selection: isnull({T_TABLE.SOME_COLUMN}).

Apparently, if we use some functions in Crystal Report, it does not get converted to the SQL query (i.e. it will not add the SQL statement with something like T_TABLE.SOME_COLUMN IS NULL. Since this checking is pretty basic, I want this condition to be pushed to the SQL statement that is generated because it should be faster to filter at database level rather than Crystal Report. Based on what I read, the possible way to push some condition to the SQL statement is to use the SQL Expression Fields. But I don't seem to be able to write T_TABLE.SOME_COLUMN IS NULL. It gives error : FROM keyword not found where expected.

Any idea how to solve this?

[EDIT]: The IsNull seems to be pushed when if it is isnull({T_TABLE.SOME_COLUMN}). But if it is some view, e.g. isnull({V_VIEW.SOME_COLUMN}), it does not get pushed.

1
sqloraclecrystal-reports
why not try something like this {T_TABLE.SOME_COLUMN}<>"" - Siva
This should work automatically the way you're doing it, and testing with Crystal Reports XI on SQL Server 2008 shows that IsNull does get translated to SQL IS NULL. Unfortunately I do not have an Oracle database server available I can test on. - user743382
@Siva: I'm not so sure whether replacing NULL with empty string will be a problem or not for me. - rcs
@hvd: I've just edited my post. It seems to be pushed when it's some table, but not if it is a view. Are you able to confirm from your side about this? Btw I'm using CR 10 with Oracle 8. - rcs
Nope, sorry, still works here when using a view. And just in case, I also tried it with a column where the server already knows it's non-nullable, but it doesn't get optimised out, it still appears in the SQL query I get. - user743382

1 Answers

0
votes

The IsNull() function is actually pushed to the SQL query, with the condition that it is not compared to some value in the Crystal Report Record Selection formula.

So IsNull({T_TABLE.SOME_COLUMN}) is converted to SQL query T_TABLE.SOME_COLUMN IS NULL. But IsNull({T_TABLE.SOME_COLUMN}) = true won't.