Data Validation Criteria

[antonyraj.deva]

Hi All,

I have a requirement in my current project to do a validation check on a key column. The column is the account key which is a 12 digit integer.

The business requirements are to check the column for the following constraints and if it fails in any one of the constraints, then the record is to be sent to the reject file.

1. If the length is less than 12 digits for e.g., 123456789 instead of 000123456789.
2. If the value has blank fields in it like "00 123456789"
3. If the value has alphabets and/or special characters in it like "0$01_3d567c9"

Can these checks be done using Datastage? I have Qualitystage and Information Analyzer also in the project. So please tell if it is doable by these softwares also.

Thanks in advance.

[chulett]

An integer is an integer so a check to ensure there are 'enough leading zeroes' doesn't really make much sense as they don't have them. Or is this coming in as a string? I would guess so based on your other criteria.

Look into the IsValid() and Length() functions... the latter could be Len(), don't recall. They should get you well on your way.

[antonyraj.deva]

Thanks Craig for the reply.

I'll try the functions as advised by you and post the results.

I guess I was not clear with the first criteria regarding the length. The example should've been like this "123678" instead of "123456089012"

Thanks again....

[chulett]

That makes more sense if you need to check that all 12 significant digits are 'used', so to speak. Again, assuming this is coming in as a string, there's also a game you can play with the Convert() function - strip out all valid values and if the original value was valid nothing should be left. Leftovers = invalid:

If Convert("0123456789","",Link.YourField) = "" Then "valid" else "invalid"

Some combination of those should cover your scenarios, I would think. Or you could go all Old School on it and after getting a good conversion from StringToDecimal() check the value is in an appropriate range: 100000000000 to 999999999999 if I've banged in all the digits correctly.

[ray.wurlod]

DataStage can do all this in one expression although, for ease of maintenance, you may like to break it up into stage variables.

Code: Select all

Len(InLink.The String) <> 12 Or Index(InLink.TheString, " ", 1) > 0 Or Len(Convert("0123456789","",InLink.TheString)) <> 0

[antonyraj.deva]

Hi Craig & Ray,

Thanks a lot for the solution.

The functions mentioned helped me to complete the validations.

I'm marking the topic as resolved and thanks a lot again.

Tony[/i]