Remove all Null values from Sql table at once

Question

I am very new to Teradata and SQL environment. I am trying to remove all the null values from sql table in teradata using ISNULL(COLUMNNAME,0). But it gives an error that "the data type does not match the defined data type name". Also, would like to know if there is any way of removing all NULL at once rather than column wise writing the above query again and again.

Answer 1

I'm not sure I completely understand what you are trying to do. ISNULL(COLUMNNAME,0) is a function that returns 0 if COLUMNNAME contains a null (it does not change anything in the table, you will have to do an update to modify the table).

The error message you get is likely because COLUMNNAME is a different type than a number. If COLUMNNAME is of type string you can use:

ISNULL(COLUMNNAME, '')

if it's of type date you need a date:

ISNULL(COLUMNNAME, DATE('1900-01-01') ) -- I just invented one here, use one that makes sense in your context

and so forth. As you noted, applying a function to a column does an implicit rename of the resulting column. Most DBMS generates a rather cryptic name so it makes sense to do an explicit rename as in:

ISNULL(COLUMNNAME, '') AS COLUMNNAME

if you want to preserve the name.

Answer 2

There's no function named ISNULL in Teradata/Standard SQL, it's called COALESCE instead:

COALESCE(columnname, 0)

And it has to be applied to each column individually.