SetNull() in Stage Variable

[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

[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?

[rwierdsm]

Is the stage variable nullable?

Can't set nullability in stage variables.

Rob

[DSguru2B]

O yea thats right, what was I thinking

[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

[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.

[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.

[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.