Specify default values for NULL values in target fields..

[gunsva]

When I am loading a target table, some fields are being explicitly set to NULL, as per the business requirements. Reporting requires that all target fields with NULL values be set to at least a single space for string fields, 0 for numeric fields.
What is the easiest way to achieve this ? Thanks.

 

[rrrkrishnan]

Use COALESCE, it is similar to nvl in Oracle.

 

[RanD]

To amplify on COALESCE -

Where I'm currently contracting the customer has multiple strings from various sources that represent NULLs of different data types. We can get NULLs, or strings for VARCHAR fields for example.

The direct example that answers your question would be

COALESCE(column, 'NO DATA')

However, frequently the COALESCE needs to be in a CASE to handle both NULLS, and various NULL representations, for
example:
<snip>
CASE
WHEN column = 'BK' THEN 'NO DATA'
WHEN column = 'NK' THEN 'NO DATA'
WHEN column = ' ' THEN 'NO DATA'
WHEN column = 'DX' THEN 'NO DATA'
ELSE COALESCE(column, 'NO DATA')
END
<snip>

The above takes in all the various VARCHAR representations of NULL, plus NULL and replaces them with a consistent NULL representation of 'NO DATA'.

Regards

Randy Volters
Certified Teradata Master
Class of '01