Search code examples
sqloraclecrystal-reports

Crystal Report: IsNull equivalent for SQL Expression

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.

Solution

  • 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.