SFDC sparse lookup with upstream Transformer.

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
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

SFDC sparse lookup with upstream Transformer.

Post by rkashyap »

While performing SFDC sparse lookup, the position of the lookup column in the input link is entered in SOQL's WHERE clause (counting from zero) e.g. ''{3}'' for 4th column.

If any column is modified/manipulated in a transformer (prior to SFDC stage) then modified column(s) move up in internal output from transformer (though not in Designer canvas), so when it comes to column position 'what you see is Not what you get'! This makes developing code with SFDC sparse lookup quite challenging.

Further, an unrelated/passthru column change (during post production maintenance) in an upstream transformer will necessitate modification of SFDC sparse lookup's WHERE clause to map to new position of lookup column.

Please share your thoughts/experiences on working around this issue.
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

Only workaround for this issue is to perform some operation on every column in the upstream transformer (e.g. append an empty string to a string column, add 0 to a numeric column) rather than mapping directly.

During version 8.5, this issue was addressed in JR47138, but it is reoccurring in DataStage 11.5.

I have raised a PMR for this issue.
rkashyap
Premium Member
Premium Member
Posts: 532
Joined: Fri Dec 02, 2011 12:02 pm
Location: Richmond VA

Post by rkashyap »

IBM has informed us ...
We should prioritize the patch JR47138 implementation based on the work effort since its not a simple fix and might need some design changes so, it might be considered for the next release of IS.
We will workaround this issue by either:
- Avoiding upstream transformer from SFDC stage OR
- Performing some operation on every column in the upstream transformer (e.g. append an empty string to a string column, add 0 to a numeric column) rather than mapping directly.
Post Reply