How to handle NULL values coming from DB side?

[Prince_Hyd]

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

[ArndW]

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.

[abhinavagarwal]

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

[chulett]

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.

[ArndW]

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.

[ray.wurlod]

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