BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

General
Enthusiast

BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

Hi

I have a table and while loading the table , if the column is INTEGER then blank from SOURCE is converted to 0 i.e. numerical ZERO.  Why is this happening ? Does this mean we cannot have BLANK value in integer column ?

The problem is I cannot identify if the 0 was sent from Source or it is converted from BLANK value.

Any suggestion ?

Thanks in advance .

I am using TD13.10

6 REPLIES
Enthusiast

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

Of course, we can have NULL values in integer column. Check your ETL code, it must be converting NULL to 0 somewhere. Also, check the table definition and see if there is "DEFAULT 0" for this column. You should change that too because while inserting if there is nothing coming from source for this column, it would default it to 0.

Junior Contributor

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

How is this table loaded?

A typecase from string to integer returns 0 for blanks/empty strings, this is documented but stupid:

try a select '' (int);

You should use a

NULLIF(col,'') (int)

to set the NULL before the cast. When you're using a load tool the syntax might be different, in FastLoad it's col (int, nullif='')

Dieter

Enthusiast

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

Thanks for the replies.

KS42982: It is not the NULL I am talking about, NULL are getting loaded as NULL only in the table. It is Blank Values ('') which are getting converted to 0 while inserting into the table.

eg: INSERT INTO TST_TABLE(col1,col2,col3) values(20,'',20);

expect output: 20,,20

actual output: 20,0,20

dnoeth: Yes, empty string is getting converted into 0. So one thing is for sure that we cannot have BLANK value in integer column. Right ? So now either I can set it to NULL or to some other value which can help me in identifying BLANK value.

Thanks :)

Enthusiast

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

I think you can still have BLANK values in the INTEGER column if you INSERT without quotes, like - INSERT INTO TST_TABLE(col1,col2,col3) values(20,,20); (assuming there is no DEFAUL value to this column)

Enthusiast

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

A blank or space cannot be inserted into a INTEGER column. Integer is for storing numeric value and not for alphabets.

A value (20,,20) will insert null for the second field. A value (20,' ',20) will result in intrinsic casting to 0.

Teradata Employee

Re: BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

select cast ('' as integer)    - this returns 0