IsNull() expression does not work in Crystal Reports 2008 SP5

[Former Member]

Dear SAP developer,

I am using Crystal Reports 2008 SP5. If I use the "Record Selection" expression/formula

isNull({SV_INQUIRY.PARENT_ID})

results are not displayed in CR even if the "Show SQL query" of CR

SELECT "SV_INQUIRY"."INQUIRY_ID", "SV_INQUIRY"."OWNER_REP", "SV_INQUIRY"."IT_SHORT_DESC", "SV_INQUIRY"."LOGGED_DT", "SV_INQUIRY"."TYPE", "SV_INQUIRY"."PARENT_ID"

FROM   "AXENT"."SV_INQUIRY" "SV_INQUIRY"

WHERE  "SV_INQUIRY"."PARENT_ID" IS  NULL

is correct and there is a result set in the Oracle Developer.

Why does the IsNull() expression not work? Which workaround can I use? Which patch shall I use?

Thanks!

[raghavendra_hullur · Accepted solution]

Hi,

Is it a string field?

Have you enabled "Convert Null values to default" under options?

Try this:

isNull({SV_INQUIRY.PARENT_ID}) or {SV_INQUIRY.PARENT_ID} = ''

Thanks,

Raghavendra

[Former Member · Accepted solution]

Hello Raghavendra,

>Is it a string field?

No, it is a number field.

>Have you enabled "Convert Null values to default" under options?

Convert Database Null values to default -> yes, set as default

Convert Other Null values to default -> tried and did not work

>Try this:

>isNull({SV_INQUIRY.PARENT_ID}) or {SV_INQUIRY.PARENT_ID} = ''

Did not work because a number is required.

Thank you!

[raghavendra_hullur · Accepted solution]

Then modify the record selection as below and check:

isNull({SV_INQUIRY.PARENT_ID}) or {SV_INQUIRY.PARENT_ID} = 0

Thanks,

Raghavendra

[Former Member · Accepted solution]

Perfect! Greetings from Germany to India!

So, is it a bug or a feature?

[raghavendra_hullur · Accepted solution]

hmm, not sure whether it's bug.

But since the nulls are converted to default value, I think the values available for a record selection will be 0 instead of null.

Thanks,

Raghavendra

[abhilash_kumar]

Hi Daniel,

What is the datasource for the report?

Are you reporting directly against a table or a Command SQL?

-Abhilash

[Former Member]

Dear Abhilash,

>What is the datasource for the report?

Oracle 11.2

>Are you reporting directly against a table or a Command SQL?

I do not use a Command SQL. It should be directly against a table.

Thanks!