Netezza Enterprise Stage outputs N instead of NULL

arpitchopra · Post by **arpitchopra** » Mon Feb 27, 2012 7:38 am

Hello Experts !

I have a very straight-forward mapping, with the following structure:
Netezza Source ---> Transformer ---> Netezza Target

I am facing a very strange situation here. A particular row has NULL value in one of the columns in the back end table. However, when i click on the "View Data" on the source and take a look at this row in the dataset, i see that this column value has been converted to N instead of NULL.

Please note that this problem occurs for 1 row only. Not for all the rows. So it looks like it is not a Null conversion error.

Hoping to hear back from you guys soon.

chulett · Post by **chulett** » Mon Feb 27, 2012 8:20 am

Sounds like it may have been converted to the word 'NULL' rather than a true null value.

arpitchopra · Post by **arpitchopra** » Mon Feb 27, 2012 8:39 am

hmmm... I thought so too. But how ?
And more importantly, how do I avoid it ?

I even extracted the query from the source stage and ran it at the back end. It still shows NULL there.

arpitchopra · Post by **arpitchopra** » Tue Feb 28, 2012 6:28 am

I have another piece of information that might be helpful. This column under question used to be of datatype CLOB in Oracle (from where this table was created in Netezza).
However, its datatype was changed to Varchar(4000) to be compatible in DS and Netezza. Can this be a reason ?

But if the table itself shows NULL then why do I see N in the source stage in DS ?

chulett · Post by **chulett** » Tue Feb 28, 2012 8:01 am

It was changed in the database or just in the job?

Honestly, I have no idea why you are seeing what you are and suspect it will take 'boots on the ground', eyeballs there to figure it out. What do you get if you select DUMP(YourColumn) from YourTable in Oracle? Have you involved your official support provider yet?

arpitchopra · Post by **arpitchopra** » Tue Feb 28, 2012 8:08 am

It was changed in the database itself.

I haven't contacted the provider yet, but looks like that is the only thing that is left to do

chulett · Post by **chulett** » Tue Feb 28, 2012 8:11 am

Still curious what a DUMP() would show...

eostic · Post by **eostic** » Tue Feb 28, 2012 10:21 am

...another thought is...."is it really null" ....and....if so......"Does it matter?"

Null is null...who cares how it is interpretted by a "display" tool.... does it satisfy true for isnull()? ..just like all the other rows that you believe are null?

If it does, then it is still NULL, regardless of what you might see. And if not....should it be NULL ?

...at least, that is the corruption I would be concerned with, as opposed to "N" ......(they might be one and the same problem --- just want to sure it is being considered from a true null-behavior point of view).

Ernie

arpitchopra · Post by **arpitchopra** » Tue Feb 28, 2012 11:12 pm

eostic wrote:...another thought is...."is it really null" ....and....if so......"Does it matter?"

Null is null...who cares how it is interpretted by a "display" tool.... does it satisfy true for isnull()? . ...

at the back end... yes.
but when i do a straight dump into a target, the same row magically has N instead of NULL.