IsValidDecimal issue in IIS8.7

prasson_ibm · Post by **prasson_ibm** » Thu Aug 29, 2013 4:34 am

Hi,
I am trying to check IsValidDecimal functionality in Datasatage 8.7.
I have developed simple job for this.

RowGen------> TSFM-------> Peek

In Transformer i generated Two columns and assigned below value.

Code: Select all

COL1=IsValidDecimal(StringToDecimal("20368.5"))
COL2=IsValidDecimal(StringToDecimal("Prasoon"))

Result i am getting as
COL1=1
COL2=1

But COL2 should be coming as 0.

Is this a bug or i am doing something wrong?

ArndW · Post by **ArndW** » Thu Aug 29, 2013 6:22 am

The StringToDecimal('blahblah') will return "0", which in turn is a valid decimal.

prasson_ibm · Post by **prasson_ibm** » Thu Aug 29, 2013 8:08 am

Hi,

Ok,then how can we validate if input is varchar and target is decimal.

ArndW · Post by **ArndW** » Thu Aug 29, 2013 8:53 am

I'm not at a DS client where I can test this, but might not

Code: Select all

IsValidDecimal("Prasoon")

work?

prasson_ibm · Post by **prasson_ibm** » Thu Aug 29, 2013 9:52 am

Hi,
I already tried this but it does not work..

chulett · Post by **chulett** » Thu Aug 29, 2013 10:57 am

What does "does not work" mean? Keep in mind the fact that all of the "IsValid" functions require a string input, the goal being to see if the string can be successfully converted to the target data type before you actually effect the conversion.

Are you saying that IsValidDecimal("Prasoon") returns TRUE?

prasson_ibm · Post by **prasson_ibm** » Thu Aug 29, 2013 12:06 pm

Hi Chullet,
I tried to test IsValidDecimal ("prasoon") and it gave output as 1.

Mayurgupta7744 · Post by **Mayurgupta7744** » Thu Aug 29, 2013 10:49 pm

Hi,

I have implemented same scenario in our project to validate whether the input value is character or integer/decimal.
For implementing the same we have use the following function in Transfomer Stage:

Syntax: IF (IsValid("Int32",INPUTCOLUMN") THEN 'Y' ELSE 'N')

When in the input value is integer/decimal it returns 'Y' otherwise it returns 'N'.

You use this logic. I am sure it will work!
Let me know if you need any more details regarding the same.

Best Regards,
Mayur

prasson_ibm · Post by **prasson_ibm** » Fri Aug 30, 2013 2:29 am

Hi
Decimal is not a valid integer, then how come you are getting 'Y' for decimals

If you are in 8.x version then it may be due to datastage Behaving differently in 8.1 or 8.5 .
check below link.

http://www-01.ibm.com/support/docview.w ... wg1JR39651

prasson_ibm · Post by **prasson_ibm** » Sun Sep 01, 2013 5:24 am

Hi,
Can someone help me to above issue.

chulett · Post by **chulett** » Sun Sep 01, 2013 8:04 am

At this point you should be involving your support provider.

SURA · Post by **SURA** » Sun Sep 01, 2013 7:19 pm

You can try If IsValid (Decimal,input) then xxx else yyy. But what you going to do if the value is not decimal.

prasson_ibm · Post by **prasson_ibm** » Mon Sep 02, 2013 4:01 am

Hi,

If the value is not decimal,i need to reject those records.

chulett · Post by **chulett** » Mon Sep 02, 2013 7:20 am

SURA's suggestion is valid - what happens if you try the base IsValid() function with the "decimal" option rather than IsValidDecimal(), do you see the same behavior? From what I've read, they are not the same - the latter is not a 'shortcut' to the former.

ArndW · Post by **ArndW** » Mon Sep 02, 2013 7:33 am

I think you made a mistake in your inital command. The following will correctly determine whether a string is a decimal or not:

Code: Select all

IsValid("decimal [12.3]",InputLinke.TestColumn)

(I had to experiment a bit to get the format in the right place...