Hello Folks
I have a source as DB and i'm getting NULL values from that so i want to replace them with char value eg(XXX) how can i perform it?
Thanks
How to handle NULL values coming from DB side?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 35
- Joined: Mon May 06, 2013 5:59 am
Option 1 - do the conversion/replacement in the DB SELECT, using DML such as "COALESCE".
Option 2 - use NullToValue(), NullToZero() or NullToEmpty() in a DataStage transform stage.
Option 2 - use NullToValue(), NullToZero() or NullToEmpty() in a DataStage transform stage.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 26
- Joined: Thu Jun 19, 2008 12:39 am
- Location: Atlanta
Hi above solution can be implemented but it depends what processing power you have with you and what data volume you have with you.
Use option 1 , while you have less CPU cycle in datastage and good CPU cycles and less data volume.
Use option 2 , while you have large data volume and moderate or good CPU cycles available at datastage.
The point is we should avoid the function "coalesce" as much we can.
Cheers
Use option 1 , while you have less CPU cycle in datastage and good CPU cycles and less data volume.
Use option 2 , while you have large data volume and moderate or good CPU cycles available at datastage.
The point is we should avoid the function "coalesce" as much we can.
Cheers
- Thanks and Regards,
Abhinav Agarwal
Abhinav Agarwal
COALESCE isn't really appropriate in this scenario, more like NVL or ISNULL in a CASE statement.
OK... you would normally use coalesce() with a series of columns to return the first non-null value. In pondering this, are we also saying it can be used with column and a fixed value? Say:
COALESCE(MyColumn,"XXX")
To be equivalent to an NVL? If so, never occurred to me to use it like that when you have a dedicated "null to value" function. Interesting.
OK... you would normally use coalesce() with a series of columns to return the first non-null value. In pondering this, are we also saying it can be used with column and a fixed value? Say:
COALESCE(MyColumn,"XXX")
To be equivalent to an NVL? If so, never occurred to me to use it like that when you have a dedicated "null to value" function. Interesting.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I seem to remember a time when that was the only available function to me, I can't recall if it was Oracle or another DB; but that's the function I remember and tend to use. Nonetheless I will almost always use the option #2 in real life.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Teradata does not have NVL() function, as discussed here - Teradata does have COALESCE() function.
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.