DSXchange

I have to implement a requirement where data from a source table has a lookup to a dimension table to get its sequence number and if i dont find something for that code, then instead i should be retrieving the sequence number of the default code (which is usually like a 'No Dimension' code)

What i am doing right now is that after the lookup stage, i have a transformer stage that checks to see the value of the sequence number returned and if it is null or 0, then to add a column called Def_Value, where i set it as 'No dimension' and then pass this value to another lookup to the same dimension table (but ofcourse with just this one default record) to get the sequence number for this default value..

But am looking for other ways to implement this to avoid 2 lookups to the same table..

Appreciate your suggestions
Thanks
J

Why?

Your specification is quite specific about needing one lookup and a second conditional lookup. Therefore you need two. You can, of course, make the second one conditional.