A function equivalent to NVL in datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India
A function equivalent to NVL in datastage
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.
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.
asdfasdf
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India
A function equivalent to COALESCE() in datastage
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.
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.
asdfasdf
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: A function equivalent to COALESCE() in datastage
Re-read Ray's first answer. You could always write your own as a build op, I suppose.prasanna2883 wrote:Iam not able to identify any function which works similar to coalesce() in oracle.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers