Space in Decimal field

samyamkrishna · Post by **samyamkrishna** » Tue Apr 21, 2009 7:25 am

The Job looks like this.

SF---------Trasformer--------------DS
The sequential file has data in a decimal field as '22 55'
actually this data has to be rejected because its a space in the decimal field.
But it reaching the ountput the same way '22 55'.
what do i do to stop this.
one example
the input has data '9 .1'
there is a space btween 9 and the . but this time data is going to output as '9.00'

somebody please help. I want both of them to be rejected.
thanks in advance
samyam

chulett · Post by **chulett** » Tue Apr 21, 2009 7:50 am

Have you tried logically rejecting them by checking with the IsValid() function? Curious if that would work in this case.

samyamkrishna · Post by **samyamkrishna** » Wed Apr 22, 2009 12:43 am

chulett wrote:Have you tried logically rejecting them by checking with the IsValid() function? Curious if that would work in this case. ...

I used the IsValid(column,%ab.cd%)
now everything is coming as 00.00 in the output.
we can try doing a substring but that will be a very large thing to do.
Is there not an easier thing.

Kryt0n · Post by **Kryt0n** » Wed Apr 22, 2009 1:00 am

try IsValid('decimal', column)

ShaneMuir · Post by **ShaneMuir** » Wed Apr 22, 2009 2:51 am

Will the Num function work in this instance? ie Num(column)

samyamkrishna · Post by **samyamkrishna** » Wed Apr 22, 2009 2:54 am

Kryt0n wrote:try IsValid('decimal', column)

tried this too same thing as before.

samyamkrishna · Post by **samyamkrishna** » Wed Apr 22, 2009 2:59 am

ShaneMuir wrote:Will the Num function work in this instance? ie Num(column)

I tried If Num(Column)=0 then Column else 1
Now all the fields are getting populated with 1.

ShaneMuir · Post by **ShaneMuir** » Wed Apr 22, 2009 3:13 am

samyamkrishna wrote: I tried If Num(Column)=0 then Column else 1
Now all the fields are getting populated with 1.

Are there any valid values being passed? It is quite possible that the Num function doesn't work for decimal values. But it was worth a try.

sbass1 · Post by **sbass1** » Wed Apr 22, 2009 3:20 am

samyamkrishna wrote:I tried If Num(Column)=0 then Column else 1
Now all the fields are getting populated with 1.

I think your logic is backwards...

The DataStage Basic Guide states (you DID reference the Basic Guide before writing your code, right???):

Syntax
NUM (expression)

Description

Use the NUM function to determine whether expression is a numeric or nonnumeric string. If expression is a number, a numeric string, or an empty string, it evaluates to true and a value of 1 is returned. If expression is a nonnumeric string, it evaluates to false and a value of 0 is returned.

A string that contains a period used as a decimal point ( . ) evaluates to numeric. A string that contains any other character used in formatting numeric or monetary amounts, for example, a comma ( , ) or a dollar sign ( $ ) evaluates to nonnumeric.

If expression evaluates to the null value, null is returned.

Since 1=True and 0=False, you could code your logic as:

Code: Select all

If Num(Column) then Column else @NULL (or whatever you want to return as non-numbers)

Or, since you're just wanting to reject invalid numbers, a stage variable such as:

Code: Select all

ValidRecord:  Num(Column)

Then a constraint of simply ValidRecord

Finally, I also tried in a test routine:

Ans = Arg1 * 1

and tested it with a variety of input. Invalid numbers always returned zero. However, a long number like 123456789.123456789 was returned as 123456789.1235, so that approach might not work. It also would reject true zeros, which you might not want.

Anyway, hope some of this helps...

chulett · Post by **chulett** » Wed Apr 22, 2009 8:26 am

samyamkrishna wrote:I used the IsValid(column,%ab.cd%)
now everything is coming as 00.00 in the output.

IsValid() is a simple boolean check to say - true or false - is this a valid value of the specified type. It does not affect the 'output' that you get unless you are using it incorrectly. Seems like your '00.00' is actually just the zero that it returns to denote 'false'.

chulett · Post by **chulett** » Wed Apr 22, 2009 8:32 am

And as Kryt0n pointed out, your syntax is wrong. Check Appendix B of the Parallel Job Developer's Guide pdf for the 'type conversion functions'.

samyamkrishna · Post by **samyamkrishna** » Wed Apr 22, 2009 11:59 pm

chulett wrote:And as Kryt0n pointed out, your syntax is wrong. Check Appendix B of the Parallel Job Developer's Guide pdf for the 'type conversion functions'.

Hi Craig Hulett.
tried all the things u told me. its still the same. the thing is in the sequential file stage itself its rounding it off.
ex
22 55 is an decimal[5,0] thats comming as it is 22 55
all the things suggested is working for this field
but
9 .1 is decimal[4,2] its coming as 9.0 and
the functions suggested here not working for this
i need this record also to be rejected. but its not happening.
because its getting rounded off at the sequential file stage only.

i was thinking of reading it a Char and checking for spaces in it and reject it and if there are no spaces convert it into decimal.
is there any better way.