DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message

Group memberships:
Premium Members

Joined: 01 Mar 2005
Posts: 205

Points: 1933

Post Posted: Thu Mar 26, 2020 12:44 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Additional info: v11.5
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?

Premium Poster

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54570
Location: Sydney, Australia
Points: 295936

Post Posted: Wed Apr 29, 2020 1:30 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours