SCD detect if match was found for derivation

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
thompsonp
Premium Member
Premium Member
Posts: 205
Joined: Tue Mar 01, 2005 8:41 am

SCD detect if match was found for derivation

Post by thompsonp »

I am using the SCD stage for a Type 2 slowly changing dimension.
One of the audit columns in the dimension is version number.
This should be set to 1 for the first occurence of a business key and then increment with each type 2 change.

The column is a Decimal(9,0) and not nullable.

I have not been able to specify a derivation in the SCD stage for this column that sets the frst record to 1.

The logic in the derivation needs to be "If no matching record found in dimension lookup then set to 1, else set to dimension lookup value + 1".

I tried testing the value from the lookup and also adding a found_flag column to the dimension lookup input which is nullable, but neither allows me to detect the absence of a match.

Is there a way to do this within the SCD stage?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Set up your Lookup stage with a reject link to capture the failed lookups, and hard-code the 1 somewhere on that stream before funnelling with the "lookup succeeded" stream.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply