Stage variable not holding NULL value

[jweir]

Hi all,

I am having trouble with NULLs and stage variables. I will lay out my three stage variables below:

svCurrSSNEqPrevSSN:

Code: Select all

DSLink2.SSN = svPrevSSN

svDeptID:

Code: Select all

If IsNull(DSLink2.DEPT_ID)  And svCurrSSNEqPrevSSN Then svDeptID Else DSLink2.DEPT_ID

svPrevSSN:

Code: Select all

If svCurrSSNEqPrevSSN Then svPrevSSN Else DSLink2.SSN

I then assigned in my derivations the column of DEPT_ID to the stage variable svDeptID. What the code above does is if the current record's SSN equals the previous SSN and the current record's DEPT_ID is NULL then the DEPT_ID column equals the svDeptID variable. If it does not meet that criteria, it will assign the svDeptID variable equal to the current value of DEPT_ID.

My crux is when the DEPT_ID field is NULL for say 3 records of the same SSN, the first record will have NULL in DEPT_ID, however the next two records have blank.

My question is why are the last two records blank instead of NULL? Why is it when I assigned the svDeptID variable to itself it will not hold a NULL and just defaults it to some value?

Thanks in advance.

[jweir]

It appears that the second and third rows are being set to the default value, which in my example I did not set a default, this the second and third rows having blank. But why would it take the default value?

[ssnegi]

svDeptID :

Code: Select all

If IsNull(DSLink2.DEPT_ID) And DSLink2.SSN = svPrevSSN Then svDeptID Else DSLink2.DEPT_ID

svPrevDeptID :

Code: Select all

if Trim(svDeptID) = '' then SetNull() else svDeptID

svPrevSSN :

Code: Select all

DSLink2.SSN

[jweir]

Not sure how this would work. It seems like I would run into the same problem as before when trying to assign multiple rows with NULL in DEPT_ID.

[chulett]

Yah, I don't really see this as a situation where you just throw code up against the screen and see what sticks. I would much rather have seen James's question answered or at least addressed in words rather than just seeing that posted.

Sorry, I don't have a proper answer for you or I would have posted something and also have no way to play and try to puzzle this out using DataStage. Hopefully someone can explain the behavior you are seeing. Any plans to put this in front of your official support provider?

[MT]

Hi jweir

It appears that the second and third rows are being set to the default value, which in my example I did not set a default, this the second and third rows having blank. But why would it take the default value?

we experienced the same bug - by the way already in DS 8.5.
Contact IBM support.

[jweir]

MT - if you experienced the same issue, what did you do to resolve it?

chulett - there has been some type of a workaround developed. Code is below. And I plan on contacting IBM tech resources then support.

Code: Select all

Following Changes were done:
Stage Variable: svDeptID Data Type Changed to VarChar

Existing Derivation:
If IsNull(Ln_srt_Sort.DEPT_ID)  And svCurrSSNEqPrevSSN Then svDeptIDElse Ln_srt_Sort.DEPT_ID

Changed to:
If IsNull(Ln_srt_Sort.DEPT_ID)  And svCurrSSNEqPrevSSN Then svDeptID Else NullToValue(Ln_srt_Sort.DEPT_ID,' ')

Out Put Column:
DEPT_ID, leave data type as it is

Existing Code:
svDeptID

Changed to:
If svDeptID = ' ' Then SetNull() Else svDeptID

[chulett]

I'm assuming their resolution was a patch to correct the behavior but that wouldn't explain why it is still an issue in your version.

[jweir]

My assumption as well chulett. I will contact IBM resources and if I get a resolution, I will update the thread. I will set post as WORKAROUND.