1

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.

  • 1
    maybe this column is not numeric (string, datetime, etc...)? if so use proper value in 2nd parameter (empty string, valid datetime literal, etc.). – Akina Sep 20 '18 at 19:37
  • Sounds like a type clash between the integer 0 that you provided as a null replacement and some other type columns such as strings. Just curious- Why do you want to replace all nulls with a 0? – SQLRaptor Sep 20 '18 at 19:39
  • Thanks Akina and SQLRaptor. I need to aggregate the fields later and having null values creates extra rows. Just a query, if i use '0' instead of 0, would aggregation functions treat it as integer? – Kuljeet Keshav Sep 20 '18 at 19:46
  • One key thing to remember for your dataset 0 may have a completely different meaning then NULL so changing any values to add one where it isn't present may give you bad results. – Joe W Sep 20 '18 at 19:55
  • @KuljeetKeshav Aggregate functions ignore NULL values, except for COUNT(*). Use COUNT(Expression) to count and ignore nulls. I would be very careful with making such a change... – SQLRaptor Sep 20 '18 at 21:35
0

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.

  • ZEROIFNULL(COLNAME) can also be used to substitute 0 in place of null. – Kuljeet Keshav Sep 21 '18 at 17:06
  • @KuljeetKeshav: Of course, but that's non-Standard, too :-) – dnoeth Sep 21 '18 at 19:27
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.

  • Thanks Lennart! I think I got my mistake. I need to use something like < select Isnull(a.Columnname, ' ') as Columnname > to make it work. – Kuljeet Keshav Sep 20 '18 at 19:53
  • That's probably correct. Applying a function to a column will do an implicit rename of the resulting column. Most DBMS will generate a cryptic name, so it almost always makes sense to explicitly rename the column as you do. I'll add some info about that to my answer. – Lennart Sep 20 '18 at 20:05
  • I'm also looking for a way to replace all nulls to 0 (or blank), say for all columns belonging to one data type? – Kuljeet Keshav Sep 20 '18 at 20:48
  • You will have to apply one function per column, there's no way around that. You might want to create a view that does the transformation for you, and then select from the view. – Lennart Sep 20 '18 at 21:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.