Page 1 of 1
SetNull() in Stage Variable
Posted: Tue Apr 10, 2007 7:19 am
by rwierdsm
Just observed some bizzare behaviour.
Performed logic similar to below in a stage variable:
Code: Select all
If InputLink.Var1 [1,1] = ' '
then SetNull()
else If Count(InputLink.Var1 , '-') = 1
then SetNull()
else InputLink.Var1
This resulted in the value from the previous row to be carried forward to the next row. Same input value went to 2 rows where the second row should have evaluated to null (triggered SetNull() logic).
When I changed to this, stage variable behaved as I expected:
Code: Select all
If InputLink.Var1 [1,1] = ' '
then ''
else If Count(InputLink.Var1 , '-') = 1
then ''
else InputLink.Var1
Stage variable defined as Varchar(30).
Fun, eh?
Rob W
Posted: Tue Apr 10, 2007 7:24 am
by DSguru2B
I have seen that behaviour too. Never chased it to the very end. Something to do with the way your stage variable is defined I believe. Is the stage variable nullable?
Posted: Tue Apr 10, 2007 7:52 am
by rwierdsm
DSguru2B wrote:Is the stage variable nullable?
Can't set nullability in stage variables.
Rob
Posted: Tue Apr 10, 2007 7:57 am
by DSguru2B
O yea thats right, what was I thinking
Posted: Tue Apr 10, 2007 8:34 am
by rwierdsm
Hopefully someone will come up with an explaination one of these days.
In the mean time, the work around above seems to be working.
Rob
Posted: Tue Apr 10, 2007 1:33 pm
by ray.wurlod
Try using an in-band null (that is, a value that is legal for the domain but not present in the data, such as Str("\",30))
You can convert this to an out-of-band null (using SetNull()) in a column derivation expression.
Integer Stage variable and setting it to null
Posted: Thu Apr 26, 2007 12:22 pm
by DS_MJ
PX 7.5.x version.
What about a Integer Stage variable and setting it to null. How does one do it...?
I do not have a premium membership access.
Thanks.
Posted: Thu Apr 26, 2007 12:42 pm
by nick.bond
Maybe you should get membership then???!
You can't set a stage variable to null in EE. Although, certainly the unpatched version of 7.52 would let you compile a job with SetNull() in it, once you have the patches a compile error is seen.
As Ray said, you can get around this by setting the stage variable to a value you know is not possible in the data, and then converting this to null, SetNull(), within the column derivation.