Page 1 of 1
Space in Decimal field
Posted: Tue Apr 21, 2009 7:25 am
by samyamkrishna
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
Posted: Tue Apr 21, 2009 7:50 am
by chulett
Have you tried logically rejecting them by checking with the IsValid() function? Curious if that would work in this case.
Posted: Wed Apr 22, 2009 12:43 am
by samyamkrishna
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.
Posted: Wed Apr 22, 2009 1:00 am
by Kryt0n
try IsValid('decimal', column)
Posted: Wed Apr 22, 2009 2:51 am
by ShaneMuir
Will the Num function work in this instance? ie Num(column)
Posted: Wed Apr 22, 2009 2:54 am
by samyamkrishna
Kryt0n wrote:try IsValid('decimal', column)
tried this too same thing as before.
Posted: Wed Apr 22, 2009 2:59 am
by samyamkrishna
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.
Posted: Wed Apr 22, 2009 3:13 am
by ShaneMuir
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.
Posted: Wed Apr 22, 2009 3:20 am
by sbass1
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:
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...
Posted: Wed Apr 22, 2009 8:26 am
by chulett
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'.
Posted: Wed Apr 22, 2009 8:32 am
by chulett
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'.
Posted: Wed Apr 22, 2009 11:59 pm
by samyamkrishna
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.