All Forums General
TD_Raj 50 posts Joined 05/10
09 Apr 2013
BLANK FROM SOURCE IS CONVERTED TO 0 while loading into Table

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

KS42982 137 posts Joined 12/12
09 Apr 2013

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.

dnoeth 4628 posts Joined 11/04
09 Apr 2013

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='')


TD_Raj 50 posts Joined 05/10
10 Apr 2013

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 :)

KS42982 137 posts Joined 12/12
11 Apr 2013

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)

anubha_harrison 8 posts Joined 11/11
30 May 2013

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.

VBurmist 96 posts Joined 12/09
10 Jun 2013

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

You must sign in to leave a comment.