A function equivalent to NVL in datastage

[prasanna2883]

Hi,

Is there any function in Datastage which works similar to coalesce() function in oracle, which picks up the first NOT_NULL value from the provided list.

Suppose we have following columns in source(SQL Server)

column1: Null
Column2:Null
Column3:abc
Column4:Null
Column5:asd

then it should pickup Column3 as output as its Non-null value.

[DSDexter]

You have source,which is a Database, Then why not restrict the record at source level if you are not interested in rest of the records?

[ray.wurlod]

There is no equivalent function to COALESCE in DataStage. You could use nested If..Then..Else construction (which is, after all, what happens inside COALESCE).

[vmcburney]

Isn't the Modify Stage NullToValue the same as Coalesce? When there is a null set it to a default value otherwise take the existing value.

[ray.wurlod]

No.

For starters, there is no NullToValue() function in the Modify stage - this is an error in the Parallel Job Developer's Guide.

There is a handle_null() function, but it only takes one field as its input argument.

A Coalesce() function takes an arbitrary number of input arguments and returns the first of these that is not null, or null if they are all null.

[vmcburney]

Got my nulltovalue mixed up with my handlenull. You can use a nulltovalue in a Transformer. A standard NullToValue would coalesce two values and you could use nested NullToValues to coalesce more than one value.

[prasanna2883]

Hi All,
Thanks for your responses. In my posting the subject was wrong. I dont want a function equivalent to NVL but I need a function equivalent to Coalesce().

NullToValues function is similar to NVL(). But I need a function which takes first not null value from the input list of values i.e., like Coalesce().

My source is SQL Server database, Iam not able to identify any function which works similar to coalesce() in oracle.

[ray.wurlod]

My first answer is unchanged. However, I believe that Oracle does have a COALESCE() function - though I can't test that at the moment.

[chulett]

Not sure where anyone said that it didn't but yes - Oracle has a COALESCE function and as people noted, it returns the first non-null value in a list of values.

[chulett]

Iam not able to identify any function which works similar to coalesce() in oracle.

Re-read Ray's first answer. You could always write your own as a build op, I suppose.

[skp]

Hi,

I think it is regarding coalesce function in SQL Server. Is there any function in sQL Server similar to coalesce.

Thanks

[chulett]

If you reply to the latest post from the OP, you'll see they changed the subject to "Re: A function equivalent to COALESCE() in datastage". That answer is still no. And a quick Google reveals that SQL Server does indeed support the ANSI standard coalesce function.