Validations

[srividhya]

Hi ,

Iam using a FTP file stage to read the input file. Th File is having 70 columns, iam reading everything as varchar. I have to validate the amount and date columns. I have to reject the amount and date columns if they are varchar and i want to keep the null amounts and null dates.
In my file I have 40 columns as amount or date.
I tried the following
1.In my transformer i have 40 stage variable and iam using the function alpha and alnum. But iam getting lot of errors
2.I used function Num to check but here the null also getting rejected.
if num(mystring)=@true then mystring else "rej"
and in my constraint iam having stagevar1 ="rej" or stagevar2="rej".......for rejected file, and stagevar1<>"rej" and stagevar2<>"rej".

3. ISNULL and ISNOTNULL is not working.I guess for the null values in my input file i dont have any space the file looks like
My input file is abc,,,,123,ghgj,,,,,

Please suggest me an efficient way to do this validation.

Thanks
Sri

[tardifma]

Hi.
I think when you read an empty string, datastage does not replace the empty string by a Null... I think you'll have to check if Trim(Var) = '' then...

For the amounts, the transformation should be like

if trim(Var1) <> '' then
if num(var1) = 1 then
StringToDecimal(Var1)
else
null
else
null

For dates...
if isValid("date", var1) then ... else...

I think that should work...

Hope this will help

[tardifma]

U can use IsValid as well for number... it should work... but I never tried...

[ArndW]

Sri,

let's take the problems you are having one at a time, that way it might turn out that you don't have that many issues but just one or two.

NULL and ISNOTNULL work. Always. Accept that as a given and you can start analyzing your data. If a string of length 0 comes from the FTP (or sequential or whatever source) then it is not null. If you put a specific import null handler on that string you can make DS convert it to null.

You state

I used function Num to check but here the null also getting rejected

. The "Num" function returns a value - if you have a derivation of "IF NUM(In.VarCharField) THEN In.VarCharField ELSE 'rej'" it should work as you expect. If it works differently you should post the string value where the NUM() doesn't work and someone in this forum might help.

[srividhya]

Hi

Thanks for your quick response.

MY "IF NUM(In.VarCharField) THEN In.VarCharField ELSE 'rej'" it is working fine. But when iam passing a null value then it is getting rejected, but i dont want the reject.
Iam not replacing the empty string with any other value so ISNULL and ISNOTNULL is not working.

Noe i will try with the new suggestion.
Thanks
Sri

[ArndW]

Hello Sri,

A NULL and an empty string are different values. Are you saying that ISNULL() and ISNOTNULL() have a bug or that they aren't working the way you expect them to?

If you are reading the values from a csv file as you showed, you will be getting empty fields and not NULL values, so your ISNULL() will always return false. And the value of NUM("") is also false.

[vinay_renu2001]

Hi Srividya

I have encountered a problem while using FTP-Plugin stage.

Can we use "_" or any Special Characters in the UserName of the FTP stage? Could you please share your expreiences

Thanks in Advance
Viny