Ahhh, Richard... let's tawk.
First, just about stage variables in general. They've been quite the boon since they were added to DataStage as they've got a ton of uses other than in the context you are concerned with right now. A couple of examples off the top of my head:
*For repeated derivations. Why repeat the same derivation logic (typically to check the success of failure of something) when you can do it once and leverage it multiple times?
*For boolean values and to make complex derivations more self-documenting. A simple example that encapsulates these two points. A lookup needs to be checked and a value compared with the input data to determine subsequent values. Instead of repeating:
Code: Select all
If Not(Lookup_link.NOTFOUND) And Lookup_link.Old_Value = Input_link.New_Value Then X Else Y
perhaps several times - consider this. Create a stage variable called GoodRecord. In the derivation of the stage variable, put just the expression:
Code: Select all
Not(Lookup_link.NOTFOUND) And Lookup_link.Old_Value = Input_link.New_Value
This evaluates to @TRUE or @FALSE and sets the value of the stage variable accordingly. Now, in the derivations of the fields that need to make decisions based on this, you can simply say:
For what you need to do, some points to keep in mind. Stage variables are evaluated
before the main derivations in the Transformer
and they happen in the order listed. So, to check for changes in repeating groups you use
two stage variables - one to hold the "old" value and one to hold the result of the comparison of old versus new. It could be as simple as a boolean variable called IsChanged and one called OldValue. Declare them in that order and then reference the OldValue in the check for IsChanged:
Code: Select all
IsChanged: Input.NewValue <> OldValue and @INROWNUM > 1
OldValue: Input.NewValue
You may or may not need the special check for the first record depending on exactly what you are checking and how you set the Initial Value of the stage variables. Does that make sense?
I make use of the "boolean" stage variables quite a bit, especially for complex Constraints. Another interesting use is for a conditional counter, something that only gets incremented under certain conditions and not necessarily for every row. Accomplish this by setting the stage variable to itself when you don't want it to change:
Code: Select all
GoodCount: If GoodRecord Then GoodCount + 1 Else GoodCount
Hope this helps peak your interest and you start leveraging Stage Variables in your jobs... you will find them
very handy, IMHO.