Losing the null value

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Losing the null value

Post by thurmy34 »

Hi gurus
In a transfomer i'm using this logic

Code: Select all

if svMKeys=1 
then if (vers_Tr01.C_CDACOLONNE="DOSSIER" AND vers_Tr01.L_CDADATASTRING="TXREGATTENDU")  
     then svDataNumber
	      else svcol22
else if (vers_Tr01.C_CDACOLONNE="DOSSIER" AND vers_Tr01.L_CDADATASTRING="TXREGATTENDU")  
     then svDataNumber
	 else setnull()
svcol22 is a variable stage define in decimal(15,2).
The first time svcol22 is set to null (that what i want) , the next time it change from null to 0.00 with the warning Numeric string expected of the appropriate decimal precision . Use default value.
Numeric string expected . Use default value.

In other word i'm losing the value of svcol22 when it contains a null

A easiest example

Code: Select all

if @INROWNUM=1 then setnull()  else svcol22
Peek_559,0: RowNum:1 svcol22:NULL
Peek_559,0: RowNum:2 svcol22: 0000000000000,00
Peek_559,0: RowNum:3 svcol22: 0000000000000,00

Thank you
Hope This Helps
Regards
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

I note that you say you are on release 8, are you on release 8.5.1 or later?

http://www-01.ibm.com/support/docview.w ... wg21514921
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

I'm in 8.7


From my point the view the probleme isn't the evaluation of the null but the lost of it when using a stage variable.

Thank you anyway.
Hope This Helps
Regards
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

If you right click to view the stage variable properties, do the data types, precision, and scale all look correct?

If that looks good, then what are the steps to reproduce the problem? I setup a little row generator but was unable to create any warnings or get any decimal value to match the behavior you described.
Choose a job you love, and you will never have to work a day in your life. - Confucius
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

Hi All

@qt_Ky
Everyhing looks correct.
To test i did
if @INROWNUM=1 then setnull() else svcol22 where svcol22 is a stage variable.

@Thomas.B
APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING is set to False.

Thank you.
Hope This Helps
Regards
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Is your svcol22 stage variable self-referencing?

I got similar results on version 11.5 test job using a row generator with 10 rows but never got any warning messages. I used similar logic with a self-referencing nullable decimal stage variable "sv" derivation:

Code: Select all

If @INROWNUM = 5 Then SetNull() Else sv
The output had 0.00 values before and after record 5. Also if I set the stage variable's initial value to some number, like 33.33, then it dumped that number out for each record before and after record 5, whereas record 5 was set to NULL.

I was expecting 33.33 to be set initially on record 1, then retain that value up through record 4, then set NULL on record 5, and then retain the NULL value in all the following records.
Choose a job you love, and you will never have to work a day in your life. - Confucius
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

Is your svcol22 stage variable self-referencing? --> yes !!!

With your test you show that the problem is not the null but the reuse of the variable ? I'm correct ?

Thank you
Hope This Helps
Regards
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Correct. It appears that stage variable re-use normally works unless the NULL value is involved.

I am on UNIX for what it may be worth. What I see from a test job in v11.3 and v11.5 is that the stage variable's initial value does get set properly, and by default for a decimal data type the initial value is zero.

When it references itself during assignment, in order to assign the stage variable's value from the previous row, that is also working as expected, except when the previous value was set to NULL, by using the SetNull() function. It behaves as if the NULL value doesn't count, so it goes back to the previous row that had a non-NULL value and uses that value instead.

I do not know if it is working as designed or if it is a defect that has been present since version 8.x. I would not expect this behavior, so it sounds like a defect to me. I wonder if anyone else can explain it?
Choose a job you love, and you will never have to work a day in your life. - Confucius
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

This was just posted a few days ago:

JR58660: In the PX transformer stage, nullable stage variables are incorrectly set to NULL at the start of every link evaluation cycle
https://www-01.ibm.com/support/entdocvi ... wg1JR58660
Error description:

In the PX transformer stage, nullable stage variables are
incorrectly set to NULL at the start of every link evaluation
cycle.

In transformers with multiple output links, this can cause
incorrect data to be written on the links.
If you're working with Support, then this may be a direct result you're already aware of. Otherwise, you need to contact Support and request a patch.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply