KBADifferent does not work Fractional part of Dec is 10 dig
Moderators: chulett, rschirm, roy
KBADifferent does not work Fractional part of Dec is 10 dig
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
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
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:
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.
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
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
![Confused :?](./images/smilies/icon_confused.gif)
"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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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).
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).
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
![Wink :wink:](./images/smilies/icon_wink.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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.
![Crying or Very sad :cry:](./images/smilies/icon_cry.gif)
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.
Last edited by kcbland on Tue Jun 26, 2007 8:38 am, edited 1 time in total.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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
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