Problem while handling Null values

hhh · Post by **hhh** » Thu Oct 13, 2005 9:22 am

Hello Datastage Gurus,

in my datastage job, i have several columns and i have handled null value for each column also.
However some records dropped for few decimal field columns, and gives warning messages like "Format_NV_Tfm,0: Field 'P_MKTPRC_BAM' from input dataset '0' is NULL. Record dropped.

Please suggest me on this issue.

Thanks and Regards
Hman

mandyli · Post by **mandyli** » Thu Oct 13, 2005 9:27 am

Hi

Pls check that column getting any NULL values. If null values possible handed null in your SQL query it. Which stage r u using? Like Transformer or aggregator Stage.

Thanks
Man

hhh · Post by **hhh** » Thu Oct 13, 2005 9:34 am

Hi,
i am migrating data from db2 stage to Sequential file. yes there is null values for some records, however i have handled the null values in Transformer stage.
Thanks
Hman

mandyli wrote:Hi

Pls check that column getting any NULL values. If null values possible handed null in your SQL query it. Which stage r u using? Like Transformer or aggregator Stage.

Thanks
Man

track_star · Post by **track_star** » Thu Oct 13, 2005 10:18 am

Apparently you didn't get them all.....

Try setting the column properties in the seq file stage so that it writes out a default value when a null is seen.

hhh · Post by **hhh** » Fri Oct 14, 2005 12:53 am

Hi ,
I transfered data from db2 stage to Transformer stage to sequential file.any way i have handlled null value in Transformer stage, i got warnings in transformer stage only, that means my records are dropped before transformer stage.Though i am able to see records while viewing DB2 stage.
Thanks

track_star wrote:Apparently you didn't get them all.....

Try setting the column properties in the seq file stage so that it writes out a default value when a null is seen.

ArndW · Post by **ArndW** » Fri Oct 14, 2005 3:47 am

hman,

but you are getting your error BEFORE you handle the nulls in your transform. The error looks like you have declared the incoming columns to be non-nullable even though they do contain null values. PX is quite picky about this, you can get away with it in a server job but not in PX.

hhh · Post by **hhh** » Fri Oct 14, 2005 4:26 am

Hi Arnd,
i have columns which have nullability NO in table defination and i have put them nullability YES in DB2 stage.
I am doing and handling in transformer stage.
Though in many such type of columns i am getting null values in the input data but they are getting process properly except few columns .
While handling the null using case statement in sql query then it works fine however i dont want to do this.
Is there any otherway to prevent such kind of warnings(Records dropped)

Thanks and Regards

ArndW wrote:hman,

but you are getting your error BEFORE you handle the nulls in your transform. The error looks like you have declared the incoming columns to be non-nullable even though they do contain null values. PX is quite picky about this, you can get away with it in a server job but not in PX.

ArndW · Post by **ArndW** » Fri Oct 14, 2005 5:00 am

The basic rule in PX jobs is that if a column might contain null values then the nullable attribute MUST be set; so in your source table you need to set those columns potentially containing nulls so that they are nullable=yes.

You can specify attributes on input to change nulls to some other character(s), either on a field or a stage basis. But PX needs to have a rule on what to do.

hhh · Post by **hhh** » Fri Oct 14, 2005 5:14 am

Hi
i have handlled explicitly like If IsNotNull(linkname.columnname ) Then linkname.columnname Else Str('0',12) : '.' : Str('0',2) in the transformer stage despite records are dropped
Thanks

ArndW wrote:The basic rule in PX jobs is that if a column might contain null values then the nullable attribute MUST be set; so in your source table you need to set those columns potentially containing nulls so that they are nullable=yes.

You can specify attributes on input to change nulls to some other character(s), either on a field or a stage basis. But PX needs to have a rule on what to do.

ArndW · Post by **ArndW** » Fri Oct 14, 2005 5:20 am

hhh,

it doesn't matter what you do in the transform, if the incoming column has nullable=no then it is dropped before it gets to your transform logic. Your error message

"Format_NV_Tfm,0: Field 'P_MKTPRC_BAM' from input dataset '0' is NULL. Record dropped.

point to this. In the INPUT definition for column P_MKT_PRC_BAM of the transform Format_NV_Tfm the column is declared as non-nullable and this is why PX is dropping the record.

hhh · Post by **hhh** » Tue Oct 18, 2005 3:15 am

Hi ArndW,
If my input column is null and i handle null with following condition then there is chance to drop records.
if isnotnull(P_MKTPRC_BAM) and P_MKTPRC_BAM < 0 Then '-' Else '+'
However if u will change the format of if condition into nested if then it will work fine.
Ex. if isnotnull(P_MKTPRC_BAM) Then if P_MKTPRC_BAM < 0 then '-' Else '+' Else '+'

ArndW wrote:hhh,

it doesn't matter what you do in the transform, if the incoming column has nullable=no then it is dropped before it gets to your transform logic. Your error message
"Format_NV_Tfm,0: Field 'P_MKTPRC_BAM' from input dataset '0' is NULL. Record dropped.
point to this. In the INPUT definition for column P_MKT_PRC_BAM of the transform Format_NV_Tfm the column is declared as non-nullable and this is why PX is dropping the record.

ArndW · Post by **ArndW** » Tue Oct 18, 2005 3:25 am

hhh,

in order to compute an AND condition both elements need to be evaluated, so you are going to perform an illegal operation on a null value in your query.

Change it to

if isnotnull(P_MKTPRC_BAM) or P_MKTPRC_BAM => 0 Then '+' Else '-'

if you really want to keep your original design, but the second variant you wrote is just as good.

kumar_s · Post by **kumar_s** » Sat Oct 22, 2005 5:39 am

Hi,
Also make sure APT_PADCHAR_STRING = 0x00 else if it is ' ' you need to check for space.

regards
kumar