Not able to compare decimal values in table comparison

[pan_cheng]

Hi Gurus,

I am not able to compare decimal values using table comparison. I have input file with Gross weight, Net Weight. I am comparing those fields with the same fields in table. In the input file i have values for Gross Weight as 4350.00 and Net weight as 35.00 and in the table also i have the value for Gross Weight as 4350.00 and Net weight as 35.00. When i compare those fields using table comparison i am getting out put as if both values are different. Pls advise.

Thanks,

[former_member187605]

I am afraid you'll have to give a bit more info. Because it works fine for me. There's no apparent reason why it shouldn't for you.

[Former Member]

Hi Drik,

I think data types used by Pan are not same thats why problem occurred

I faced the same issue in past.

Drik,Whats your knowledge for same?.

[pan_cheng]

Thanks for your response. I extracted material number, gross weight and netweight into staging table. For example 1234,30.12,12.12 are the material number gross weight and netweight from ECC and all these values are stored as varchar. I got an input file as material number,grossweight and netweight as 1234,30.12,12.12. All the fields also declared as varchar

After doing table comparision on the gross weight and netweight using material number, table comparision is saying there is change in gross weight and netweight even though the values in input file and the table are same. I think we can't use real values as comparison columns. Am i wrong. Please suggest.

Thanks in Advance,

[former_member187605]

So, your title post is a bit misleading . You're actually not comparing decimals (or reals, for that matter) but varchars.

Can you check for leading or trailing blanks? That's the only reason for the behavior of your data flow I can think of.

[pan_cheng]

Hi Derek,

I was extracting decimal fields of Gross Weight,Net Weight from ECC and input file as data type varchar and comparing those fields in table comparison. Table comparison giving output, as if the field values are different even though when they are same. The following quote in BODS manual made me think whether comparison of fields with real values work or not. "Use caution when using columns of data type real in this transform. Comparison results are unpredictable for this data type". I was able to fix the issue by casting both input fields of varchar into decimal fields and compare them after that. That is working for me. Thanks for your suggestions and input.

Thanks,

[former_member187605]

That's because two real numbers that are a bit different might display exactly the same. So you may think they are equal, but in fact internally they are not. All depends on the precision.used and this is not a DS feature but common to all software products.

As said before, you are not comparing reals but character strings. So that behavior does not apply to your data flow. By casting to a decimal you are removing extraneous blanks.

[Former Member]

make sure that Input file and your target table should have same data type ex. Number(10,2).

Otherwise just validate the job once,you will get some warnings.

read out those warnings or you can attach the snapshot of same so that I can help.