I want to implement a loop in the transformer stage in datastage for a column name Col1(varchar).
Source Column = SrcCol1 (varchar)
condition is :
X =7, Y = 1
{Set n = 0
Loop (until I get a match on doing the below lookup) or (X-n = Y)
Lookup Substring (SrcCol1, 1,X-n) in a Dim1 table
n = n+1
Endloop}
If match not found then Populate "NOT FOUND" in Col1
Please help me with this loop thing !!
Loop condition in Transformer Stage in DS
Moderators: chulett, rschirm, roy
Where are X and Y coming from?
You could do a Transformer stage loop to output one row per substring of the source column. Loop until your X-n=Y. Substring can be done like SrcCol1[1,X-n].
After that use a Lookup stage for the Dim1 table. Set the lookup failure condition to continue which assigns NULL when not found. After that another Transformer stage to check If IsNull(Col1) Then 'NOT FOUND'...
If you need a wildcard lookup, then youcan use a Lookup stage and set the Dim1 table stage's lookup type to sparse (believe that works for DB2 and Oracle). With user-defined SQL you can change the SQL to use LIKE instead of = to do the lookup. What I've found in this case is that in the previous Transformer stage, I have to surround the column with '%' signs, as in '%':src.col1:'%'.
You could do a Transformer stage loop to output one row per substring of the source column. Loop until your X-n=Y. Substring can be done like SrcCol1[1,X-n].
After that use a Lookup stage for the Dim1 table. Set the lookup failure condition to continue which assigns NULL when not found. After that another Transformer stage to check If IsNull(Col1) Then 'NOT FOUND'...
If you need a wildcard lookup, then youcan use a Lookup stage and set the Dim1 table stage's lookup type to sparse (believe that works for DB2 and Oracle). With user-defined SQL you can change the SQL to use LIKE instead of = to do the lookup. What I've found in this case is that in the previous Transformer stage, I have to surround the column with '%' signs, as in '%':src.col1:'%'.
Choose a job you love, and you will never have to work a day in your life. - Confucius