KBADifferent does not work Fractional part of Dec is 10 dig

[DS4DRIVER]

I am using KBADifferent, one of Ken Blands Server routine that Checks to see if 2 values are different or same.
While using this to compare 2 Decimal values that are equal, if the Fractional part is 10 digits in one of the 2 values, the routine returns 1. If the Fractional part is less than 10 or greater than 10 digits, it works fine.

Example,
Arg1: 234.1334900000 ---> (10 digits in Fractional part)
Arg2: 234.13349
Result: 1

Arg1: 234.13349000 (8 digits in Fractional part)
Arg2: 234.13349
Result: 0

Arg1: 234.1334900000000 (13 digits in Fractional part)
Arg2: 234.13349
Result: 0

If i supress the trialing 0's, then it would work. But, i am trying to find a way to supress 0's on both the source (seq file) and the target (DB2)before using KBADifferent to compare the values.

This is KBADifferent code:
DataIn1 = TRIM(Arg1)
DataIn2 = TRIM(Arg2)
If (IsNull(DataIn1) and Not(IsNull(DataIn2))) or (IsNull(DataIn2) and Not(IsNull(DataIn1))) Then
Ans = @TRUE
End Else
If IsNull(DataIn1) and IsNull(DataIn2) Then
Ans = @FALSE
End Else
If DataIn1 = DataIn2 Then
Ans = @FALSE
End Else
Ans = @TRUE
End
End
End

[kcbland]

Where did you dig up that old piece of code? That must be 8 years old! If you're comparing strings it's okay to use that logic. But if you're comparing numbers you have to consider the number of decimal places. The function you're using is limited to character strings and "average" type numbers.

You can't compare past your PRECISION setting using an equal sign. By default PRECISION is 4, but for DS I think they set it to the maximum, which is 9.

You need to look in the DS BASIC manual and either enhance your function or write a new one to handle long numbers and extended decimal places.

The SCMP (String Compare) is used to handle very long numbers. SADD (String Addition) is also similarly used. For example, the DS BASIC manual shows:

Code: Select all

Example
A = 88888888888888888
B = 77777777777777777
X = "88888888888888888"
Y = "77777777777777777"
PRINT A + B
PRINT SADD(X,Y)
This is the program output:
166666666666667000
166666666666666665

This shows that normal math operations have limits on the size of the number. SCMP may do the trick for you, but read the notes on it because you can ONLY use it when you're dealing with numbers.

[ray.wurlod]

FWIW the maximum documented value of PRECISION is 9 but the documentation is incorrect (and has been since 1984). The actual maximum is 14.

[chulett]

And this is different from the precision specified by the EXACTNUMERIC uvconfig parameter?

"Specifies the number of digits of precision before rounding occurs. The maximum is 57 digits and the minimum and the default is 15 digits."

Are we actually discussing scale?

[ray.wurlod]

We are not discussing scale.

We are discussing an artifact of working in a data-typeless environment, where decimal numbers are rounded to PRECISION decimal places following any arithmetic operation (originally so that display columns are not too wide).

For most currency operations, PRECISION 4 is fine. So this is the default (except for I-descriptors, where the default is 7).

[chulett]

Ok. The original post led me a little astray but I see regardless of scale it's a precision issue.

As Ken notes, I've had to switch to the 'string math' functions (and write an 'SMOD' routine) when working with numbers larger than the EXACTNUMERIC setting, which defaults to 15 not 4 or 9 or 7 or 14. But the setting you two are discussing seems to be something else, yes?

You didn't say if/how this is different from the EXACTNUMERIC setting. If it is different, is there any kind of a configurable parameter that controls this? Mentioning what the 'default' is seems to imply the ability to change it.

[DS4DRIVER]

I shall look into the Basic document to come up with a solution.
This is one of Ken's routines that is still used at my Client' s place. He came over to this Client in 2004 to introduce his Framework and Methodologies.

[DS4DRIVER]

Is there a way to Supress Trialing 0's?

[ray.wurlod]

PRECISION <> EXACTNUMERIC

PRECISION is a statement that can be inluded in a BASIC routine.
EXACTNUMERIC is a configuration directive in uvconfig

They are related by the fact that PRECISION must be less than EXACTNUMERIC but it remains the case that PRECISION must also be less than 15.

[chulett]

Thanks Ray.

[chulett]

Is there a way to Supress Trialing 0's?

Math. Add zero to the field, for instance.

[kcbland]

Unless you're dealing with very precise small numbers, you can normally use an "=" or "<>" when doing comparisons for both strings and numbers.

Most ETL work involves text fields and 0, 2 or 4 decimal place numbers. For most of the ETL work we do, we never have to handle 10 to 16 decimal place values or super huge numbers.

The problem with putting together a standard library is that you either write functions that handle every situation possible or have a set of functions and use accordingly.

I try to give my customers a "starting point" library. Either enhance these functions to cover all needs, or make copies of these functions and alter each copy to fit specific needs and use accordingly. The problem with one-size-fits-all functions is that they tend to be more intensive and may be overkill. The problem with one-function-per-situation means that you have to pick the correct function for the situation and sometimes you guess wrong and load truncated data.

For me it's a lose-lose situation. programmers assume the library is complete, especially if they are introduced to it after I've left the building. They miss all of those those times I kept saying "modify to taste" when talking about the library.

In your function you can up the precision with a PRECISION 14 statement at the top, but the values are being passed in from DataStage, they may not have the trailing decimals prior to entering the function anyway. Test to make sure it works correctly by making sure the first row of data has sufficient example data to verify the function received 15 decimal places on the way in.

[ray.wurlod]

Try PRECISION 14 (the maximum legal value).

[DS4DRIVER]

Using PRECISION 15 throws warnings. the max value for PRECISION is 14.
Anyways, I still had issues dealing with large values in Integer part of the Decimal values. Ex 6689722.9700000000

I used SCMP. It really works (But, must Check if the value is Numeric using NUM function before using SCMP).

The code now is:

PRECISION 14
DataIn1 = TRIM(Arg1)
DataIn2 = TRIM(Arg2)

If (IsNull(DataIn1) and Not(IsNull(DataIn2))) or (IsNull(DataIn2) and Not(IsNull(DataIn1))) Then
Ans = @TRUE
End Else
If IsNull(DataIn1) and IsNull(DataIn2) Then
Ans = @FALSE
End Else
If Num(DataIn1) = 1 And Num(DataIn2) = 1 Then
If SCMP(DataIn1,DataIn2) = 0 Then
Ans = @FALSE
End Else
Ans = @TRUE
End
End Else
If DataIn1 = DataIn2 Then
Ans = @FALSE
End Else
Ans = @TRUE
End
End
End
End

[DS4DRIVER]

Using the following code throws Phantom Warning.
The content of the Warning is
Program "DSU.FMDifferentExtd": Line 21, Nonnumeric data when numeric required. Zero used.
Program "DSU.FMDifferentExtd": Line 21, Nonnumeric data when numeric required. Zero used.
Program "DSU.FMDifferentExtd": Line 21, Nonnumeric data when numeric required. Zero used.
Program "DSU.FMDifferentExtd": Line 21, Nonnumeric data when numeric required. Zero used.

My concern is, would this be an issue that could abort the job in Prod?.

This is the code:

PRECISION 14
DataIn1 = TRIM(Arg1)
DataIn2 = TRIM(Arg2)


If (IsNull(DataIn1) and Not(IsNull(DataIn2))) or (IsNull(DataIn2) and Not(IsNull(DataIn1))) Then
Ans = @TRUE
End Else
If IsNull(DataIn1) and IsNull(DataIn2) Then
Ans = @FALSE
End Else
If (Not(NUM(DataIn1)) and NUM(DataIn2)) Or (Not(NUM(DataIn2)) and NUM(DataIn1)) Or (Not(NUM(DataIn1)) and Not(NUM(DataIn2))) Then
If DataIn1 = DataIn2 Then
Ans = @FALSE
End Else
Ans = @TRUE
End
End Else
If SCMP(DataIn1,DataIn2) = 0 Then
Ans = @FALSE
End Else
Ans = @TRUE
End
End

End

End