IsNull - This function cannot be used within a custom function.

[kwtx04rm]

Greetings:

I am using CR10 Pro & Datasources are DB2, MS SQL Server 2K and Oracle.

I tried to create a custom function using IsNull to check for a NULL datetime field value but this message displays "This function cannot be used within a custom function."

Is there some other syntax that can be used in the function to test for IsNull?

Here is the function:
====================
Function (numbervar AssignmtInUse, datetimevar LastScanDateNull)

If AssignmtInUse = 0
And IsNull(LastScanDateNull)
Then 1
Else 0

 

[synapsevampire]

Isnullsmust be used first in formulas, perhaps in functions too, I rarely create functions.

So don't use a function, just use a formula:

If IsNull(table.LastScanDateNull)
and
{table.AssignmtInUse} = 0 Then
1
Else
0

Sounds like a purdy fugly report with all of those datasources in one report.

-k

 

[kwtx04rm]

Thank you synapsevampire for quick response.

Please pardon me, I was not too clear about datasources. This report only uses DB2...the other two are used in creating other reports.

Yes, I agree with you about using a formula vs. a custom function. However, we've been asked to build this report based on custom funtions...but if it will not be possible to use IsNull within the custom function we may have no other choice.

Regards.

 

[synapsevampire]

Create a SQL Expression on the database that handles the null check an reference the SQL Expression.

Can't recall the DB2 syntax off the top o my headbut it should be something like:

iif(table.field, null,1,0)

Nowyou can just test for the sql expression being 1 or 0 in lieu of a null check.

Assuming it handles IIFs, what ever the DB2 means, use it and test, should get you around it.

Another means is to set the report options to convert null values to default, so then you could test for the default value instead of a null.

-k

 

[synapsevampire]

By SQL Expression on the database, I meantwhich is process on the database, if you're unfamiliar with SQL Expressions, they are a typeof formula in Crystal for using your database SQL to return values - extremely powerful.

-k