DSXchange

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

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.

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

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.

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.

Teradata does not have NVL() function, as discussed here - Teradata does have COALESCE() function.