Need to Perform Logic in Transformer

[DS_MJ]

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

[priyadarshikunal]

First of all, what have you tried and what is the problem you are facing?

[DS_MJ]

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

[chulett]

You haven't posted anything to show you could use the Field() function here. Is your source any kind of delimited string? If not then you'll need to stick to substring.

[DS_MJ]

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

[priyadarshikunal]

if = if
between = ">=" and "<="
case when = nested if then else
substring = []

[DS_MJ]

Appreciate your quick response.
However, can you please help write it.

Thanks.

[ray.wurlod]

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 ""

There is no solution involving the Field() function.

[DS_MJ]

Thank you Ray.
I am using 8.7
However, I dont see the function under Type conversion as "IsValidInteger"
Currently I am getting IsValidInteger is not defined.

Thanks.

[ray.wurlod]

Sorry, that's a local modification here. You can use IsValidDecimal() in the same way.

[priyadarshikunal]

I think the solution will return valid number values, however the requirement is to check if it is not number.

Try this

Code: Select all

if len(convert('0123456789','', left (MNO, 3))) =0 then " " else convert('0123456789','', left (MNO, 3))

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.

[ray.wurlod]

the requirement is to check if it is not number.

No it isn't. Read the original post again.

[priyadarshikunal]

This is what the OP wrote in a third post.

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.

[priyadarshikunal]

Along with that there are too many NOT's in the query to make sure its not number. Unless I missed something which you noticed. Please enlighten in that case.

[chulett]

Lagomorph bifurcation.