Need to Perform Logic in Transformer
Moderators: chulett, rschirm, roy
Need to Perform Logic in Transformer
Hello:
Need to Perform the following logic in Transformer:
If NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,2,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,3,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,3)
WHEN NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,2,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,2)
WHEN NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,1)
ELSE '' ''
END
I thought of creating a stage Variable and then call this Stage variable int he transformer.
But how do I do the above using If then Else and Field function?
Thanks.
MJ
Need to Perform the following logic in Transformer:
If NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,2,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,3,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,3)
WHEN NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
AND NOT SUBSTR(MNO,2,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,2)
WHEN NOT SUBSTR(MNO,1,1) BETWEEN ''0'' AND ''9''
THEN SUBSTR(MNO,1,1)
ELSE '' ''
END
I thought of creating a stage Variable and then call this Stage variable int he transformer.
But how do I do the above using If then Else and Field function?
Thanks.
MJ
Thanks in advance,
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
I was thinking to put this in stage variable and use the Field/Index function... but how to go about doing it is the question.
Could use someguidence on how to convert the above logic in Datastage.
Thanks
Could use someguidence on how to convert the above logic in Datastage.
Thanks
Last edited by DS_MJ on Wed May 29, 2013 2:23 pm, edited 1 time in total.
Thanks in advance,
Oh Sorry.
This field MNO is a VARCHAR of length 15 and has no delimiters in it. It is a combination of Characters and Numbers.
So if the first 3 fields are Character then we need to extract it to create a Prefix.
Need to select Positions that are not Numeric. If all positions are numeric the Prefix will be spaces.
This field - MNO contains many different formats. Each position must be evaluated individually hence the above query.
My Source is a dataset. I will use the transform to process this and other conversion and put it into a dataset.
DATASET -------> TRANSFORM ----------> DATASET
This field MNO is a VARCHAR of length 15 and has no delimiters in it. It is a combination of Characters and Numbers.
So if the first 3 fields are Character then we need to extract it to create a Prefix.
Need to select Positions that are not Numeric. If all positions are numeric the Prefix will be spaces.
This field - MNO contains many different formats. Each position must be evaluated individually hence the above query.
My Source is a dataset. I will use the transform to process this and other conversion and put it into a dataset.
DATASET -------> TRANSFORM ----------> DATASET
Last edited by DS_MJ on Wed May 29, 2013 2:36 pm, edited 1 time in total.
Thanks in advance,
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
If IsValidInteger(Left(InLink.MNO,3)) Then Left(InLink.MNO,3) Else If IsValidInteger(Left(InLink.MNO,2)) Then Left(InLink.MNO,2) Else If IsValidInteger(Left(InLink.MNO,1)) Then Left(InLink.MNO,1) Else ""
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
I think the solution will return valid number values, however the requirement is to check if it is not number.
Try this
Also you can use convert('0123456789','', left (MNO, 3)) in a stage variable.
PS: This is not my idea as I did not go through the entire logic, my bad, some one already replied to your mail on ittoolbox group. It was not visible correctly as quote was showing as " hence I mentioned it here. I think this code will work just fine.
Try this
Code: Select all
if len(convert('0123456789','', left (MNO, 3))) =0 then " " else convert('0123456789','', left (MNO, 3))
PS: This is not my idea as I did not go through the entire logic, my bad, some one already replied to your mail on ittoolbox group. It was not visible correctly as quote was showing as " hence I mentioned it here. I think this code will work just fine.
Last edited by priyadarshikunal on Fri May 31, 2013 6:29 am, edited 1 time in total.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
This is what the OP wrote in a third post.
DS_MJ wrote:So if the first 3 fields are Character then we need to extract it to create a Prefix.
Need to select Positions that are not Numeric. If all positions are numeric the Prefix will be spaces.
Priyadarshi Kunal
Genius may have its limitations, but stupidity is not thus handicapped.
Genius may have its limitations, but stupidity is not thus handicapped.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI