how to use isnull

[chooi]

Hi all,
if i use isnull function in ETL on a numeric field?it will hit the error?Urgent one. Please help!!!

[nick.bond]

IsNull() Will only return true if the value provided is null, it doesn't matter what data type the field is.

[roy]

Hi,
It might help to state the expression you use and the exact error you get so we might give a more helpfull reply.

[ray.wurlod]

The IsNull() function can take any data type as its argument, and returns 1 (= true) if the argument is null, or 0 (= false) if the argument is not null.

The only time you can get an error - which is actually a warning - is if you try to use an unassigned variable as the argument. This can happen because of a spelling or casing error. In the following example the two variable names (Amount and amount) are different.

Code: Select all

FUNCTION AddTax(Amount, TaxRate)
If IsNull(amount)
Then
   Ans = @NULL
End
Else
   Ans = Amount * (1 + TaxRate / 100)
End
RETURN(Ans)

In this case you get a warning about an unassigned variable, referring to the "amount" variable.

[arun_im4u]

The isnull function can be used to reject rows in the transformer. Use the IsNull() to reject rows which have null value. The syntax would be:

If IsNull(column_name) then REJECTED else Not(REJECTED).