| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Coalesce function rounding error |
« View previous topic :: View next topic » |
| Author |
Message
|
| koobz |
 Posted: Tue Mar 26, 2013 3:50 am Post subject: Coalesce function rounding error |
 |
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
I'm having problems with the Coalesce function, which seems to be rounding down some decimals (Message Broker version is 6.0.0.5)
I have ESQL code that runs
| Code: |
WHILE LASTMOVE(inRef) DO
INSERT INTO Database.Trans(
...
REGULARHRS,
OTHRS,
..
)
VALUES (
...
COALESCE(inRef.NS2:RegularHours,0),
COALESCE(inRef.NS2:OvertimeHours,0),
...
)
END WHILE; |
And I am receiving XML in the format of:
| Code: |
<ns2:Transactions>
<ns2:Transaction>
...
<ns2:OvertimeHours>1.51</ns2:OvertimeHours>
...
</ns2:Transaction>
<ns2:Transaction>
...
<ns2:RegularHours>1.59</ns2:RegularHours>
....
</ns2:Transaction>
...
</ns2:Transactions>
|
In one Transactions element there are many Transaction elements. If the first transaction element is Overtime then all the RegularHours are rounded down to an integer in the database, but all the Overtime values are persisted as decimals. And vice versa.
I've tested it and if the first transaction was received as:
| Code: |
<ns2:Transaction>
...
<ns2:OvertimeHours>1.51</ns2:OvertimeHours>
<ns2:RegularHours/>
...
</ns2:Transaction> |
Then all values for both OT and Regular Time for all transactions are persisted as decimals - unfortunately I don't control the code generating the XML we are sent.
Wondered if anyone else had come across similar?
I will try changing the call to COALESCE to:
COALESCE(inRef.BBW1NS:RegularHours,0.00) and if that doesn't work I will rewrite it as an if statement, but to me this looks like a bug regardless - why should the coalesce function behave/round differently based on a previous iteration? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Tue Mar 26, 2013 4:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Take a user trace.
What's happening is not what you think is happening, but I can't tell what is actually happening. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Mar 26, 2013 4:37 am Post subject: Re: Coalesce function rounding error |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| koobz wrote: |
| Message Broker version is 6.0.0.5) |
#include <standard blurb about OoS version.h>
| koobz wrote: |
| this looks like a bug regardless |
And if it is, it could have been fixed years ago & you'd never know.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| koobz |
| Posted: Tue Mar 26, 2013 4:48 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
Vitor - appreciate that. If it was my choice we would get a more up to date version. Perhaps if I can prove this is a bug with this version I will have a little more ammo to persuade the organ grinder 
mqjeff - will do that now, thanks for your suggestion... |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Mar 26, 2013 5:00 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
What are the datatypes of these two fields in the DB? This can also have an effect?
What happens if you use a tool like db2cc, SQLServer Admin etc and manually issue the insert? using the values 1.51 and 1.59 (remove the coalesce)
What values are actually inserted?
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Mar 26, 2013 5:01 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| koobz wrote: |
| If it was my choice we would get a more up to date version. Perhaps if I can prove this is a bug with this version I will have a little more ammo to persuade the organ grinder |
I didn't really think you'd made a choice to use that. Aside from the most excellent suggestion of my most worthy associate, you could also use my comment as a rock to throw. You could prove conclusively that it's a problem with the software; indeed you could prove that the software fails to start after a patch to the OS due to it doing something clearly forbidden by that version of the OS and what do you do? You (in the sense of the organ grinder) can't get an APAR from IBM or any other support to get it working. If it's supporting a key business function that could be embarrasiing.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| koobz |
| Posted: Tue Mar 26, 2013 6:51 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
OK - so I can't explain it but the MQ Trace looks fine. When I run the code using COALESCE (X, 0.00) it works fine - all values appear in the database (Oracle 10g) without rounding.
When I run the code using COALESCE (X, 0) the values in the trace all look correct but the value for REGULARHRS in the last insert statement below, 1.49, gets rounded to 1.
Note that in the 3rd statement the value passed for REGULARHRS is 0 as expected - maybe this is somehow affecting the precision of the next value passed to REGULARHRS (1.49)
So an issue with the ODBC datasource? or how Message Broker calls the datasource?
Anyway I can get it to work by changing the code to use COALESCE(X,0.00)...
...
Using COALESCE (X, 0.00)
<UserTrace timestamp="2013-03-26 13:09:17.772386" thread="4860" function="SqlExternalDbStmt::executeStatement"
type="ComIbmComputeNode" name="XXXX_LabourTransactionToDB#FCMComposite_1_7"
label="XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS" text="'Executing statement '%1' with inserts '%2',
%3'" catalog="BIPv600" number="2544" file="F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp" line="154">
<Insert type="string">'.XXXX_LabTrans_Service_IN_LABTRANS.Main'</Insert>
<Insert type="string">'62.4'</Insert>
<Insert type="string">'INSERT INTO XXXX_IN_LABTRANS(MESSAGEID, SOURCE, TRANSDATE, LABORCODE, SITEID, WONUM, CRAFT, OTSCALE,
REGULARHRS, OTHRS, ENTERBY, ENTERDATE, STARTDATE, TRANSTYPE, GENAPPRSERVRECEIPT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?)'</Insert>
<Insert type="string">'messageId || '_' || CAST(I AS CHARACTER), UPPER(inRef.NS2:Source), inRef.NS2:TransactionDate,
inRef.NS2:LabourCode, UPPER(inRef.NS2:SiteId), inRef.NS2:WorkOrderNumber, UPPER(inRef.NS2:Craft),
inRef.NS2:OvertimeScale, COALESCE(inRef.NS2:RegularHours, 0.00), COALESCE(inRef.NS2:OvertimeHours, 0.00),
UPPER(inRef.NS2:EnteredBy), inRef.NS2:EnteredDate, inRef.NS2:StartDate, UPPER(inRef.NS2:TransactionType),
COALESCE(UPPER(inRef.NS2:GenerateApproverServiceReceipt), 'N')'</Insert>
<Insert type="string">''16073500005.000_1', 'ZZZZ', NULL, 'WWWW, Andrew', 'YYYY', '160735.00', 'ELECTRICIAN', 2.00,
0.00, 1.51, NULL, NULL, TIMESTAMP '2012-07-09 00:00:00', 'WORK', 'N''</Insert>
<Insert type="string">XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS</Insert>
</UserTrace>
<UserTrace timestamp="2013-03-26 13:09:17.903917" thread="4860" function="SqlExternalDbStmt::executeStatement"
type="ComIbmComputeNode" name="XXXX_LabourTransactionToDB#FCMComposite_1_7"
label="XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS" text="'Executing statement '%1' with inserts '%2',
%3'" catalog="BIPv600" number="2544" file="F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp" line="154">
<Insert type="string">'.XXXX_LabTrans_Service_IN_LABTRANS.Main'</Insert>
<Insert type="string">'62.4'</Insert>
<Insert type="string">'INSERT INTO XXXX_IN_LABTRANS(MESSAGEID, SOURCE, TRANSDATE, LABORCODE, SITEID, WONUM, CRAFT, OTSCALE,
REGULARHRS, OTHRS, ENTERBY, ENTERDATE, STARTDATE, TRANSTYPE, GENAPPRSERVRECEIPT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?)'</Insert>
<Insert type="string">'messageId || '_' || CAST(I AS CHARACTER), UPPER(inRef.NS2:Source), inRef.NS2:TransactionDate,
inRef.NS2:LabourCode, UPPER(inRef.NS2:SiteId), inRef.NS2:WorkOrderNumber, UPPER(inRef.NS2:Craft),
inRef.NS2:OvertimeScale, COALESCE(inRef.NS2:RegularHours, 0.00), COALESCE(inRef.NS2:OvertimeHours, 0.00),
UPPER(inRef.NS2:EnteredBy), inRef.NS2:EnteredDate, inRef.NS2:StartDate, UPPER(inRef.NS2:TransactionType),
COALESCE(UPPER(inRef.NS2:GenerateApproverServiceReceipt), 'N')'</Insert>
<Insert type="string">''16073500005.000_2', 'ZZZZ', NULL, 'WWWW, Andrew', 'YYYY', '160735.00', 'ELECTRICIAN', 1.00,
1.49, 0.00, NULL, NULL, TIMESTAMP '2012-07-09 00:00:00', 'WORK', 'N''</Insert>
<Insert type="string">XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS</Insert>
</UserTrace>
Using COALESCE (X, 0)
<UserTrace timestamp="2013-03-26 13:53:13.989092" thread="7404" function="SqlExternalDbStmt::executeStatement" type="ComIbmComputeNode" name="XXXX_LabourTransactionToDB#FCMComposite_1_7" label="XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS" text="'Executing statement '%1' with inserts '%2', %3'" catalog="BIPv600" number="2544" file="F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp" line="154">
<Insert type="string">'.XXXX_LabTrans_Service_IN_LABTRANS.Main'</Insert>
<Insert type="string">'62.4'</Insert>
<Insert type="string">'INSERT INTO XXXX_IN_LABTRANS(MESSAGEID, SOURCE, TRANSDATE, LABORCODE, SITEID, WONUM, CRAFT, OTSCALE, REGULARHRS, OTHRS, ENTERBY, ENTERDATE, STARTDATE, TRANSTYPE, GENAPPRSERVRECEIPT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'</Insert>
<Insert type="string">'messageId || '_' || CAST(I AS CHARACTER), UPPER(inRef.NS2:Source), inRef.NS2:TransactionDate, inRef.NS2:LabourCode, UPPER(inRef.NS2:SiteId), inRef.NS2:WorkOrderNumber, UPPER(inRef.NS2:Craft), inRef.NS2:OvertimeScale, COALESCE(inRef.NS2:RegularHours, 0), COALESCE(inRef.NS2:OvertimeHours, 0), UPPER(inRef.NS2:EnteredBy), inRef.NS2:EnteredDate, inRef.NS2:StartDate, UPPER(inRef.NS2:TransactionType), COALESCE(UPPER(inRef.NS2:GenerateApproverServiceReceipt), 'N')'</Insert>
<Insert type="string">''16073500005.000_1', 'ZZZZ', NULL, 'WWWW, Andrew', 'YYYY', '160735.00', 'ELECTRICIAN', 2.00, 0, 1.51, NULL, NULL, TIMESTAMP '2012-07-09 00:00:00', 'WORK', 'N''</Insert>
<Insert type="string">XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS</Insert>
</UserTrace>
<UserTrace timestamp="2013-03-26 13:53:14.294574" thread="7404" function="SqlExternalDbStmt::executeStatement" type="ComIbmComputeNode" name="XXXX_LabourTransactionToDB#FCMComposite_1_7" label="XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS" text="'Executing statement '%1' with inserts '%2', %3'" catalog="BIPv600" number="2544" file="F:\build\S600_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp" line="154">
<Insert type="string">'.XXXX_LabTrans_Service_IN_LABTRANS.Main'</Insert>
<Insert type="string">'62.4'</Insert>
<Insert type="string">'INSERT INTO XXXX_IN_LABTRANS(MESSAGEID, SOURCE, TRANSDATE, LABORCODE, SITEID, WONUM, CRAFT, OTSCALE, REGULARHRS, OTHRS, ENTERBY, ENTERDATE, STARTDATE, TRANSTYPE, GENAPPRSERVRECEIPT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'</Insert>
<Insert type="string">'messageId || '_' || CAST(I AS CHARACTER), UPPER(inRef.NS2:Source), inRef.NS2:TransactionDate, inRef.NS2:LabourCode, UPPER(inRef.NS2:SiteId), inRef.NS2:WorkOrderNumber, UPPER(inRef.NS2:Craft), inRef.NS2:OvertimeScale, COALESCE(inRef.NS2:RegularHours, 0), COALESCE(inRef.NS2:OvertimeHours, 0), UPPER(inRef.NS2:EnteredBy), inRef.NS2:EnteredDate, inRef.NS2:StartDate, UPPER(inRef.NS2:TransactionType), COALESCE(UPPER(inRef.NS2:GenerateApproverServiceReceipt), 'N')'</Insert>
<Insert type="string">''16073500005.000_2', 'ZZZZ', NULL, 'WWWW, Andrew', 'YYYY', '160735.00', 'ELECTRICIAN', 1.00, 1.49, 0, NULL, NULL, TIMESTAMP '2012-07-09 00:00:00', 'WORK', 'N''</Insert>
<Insert type="string">XXXX_LabourTransactionToDB.XXXX_IN_LABTRANS</Insert>
</UserTrace> |
|
| Back to top |
|
|
| koobz |
| Posted: Tue Mar 26, 2013 7:01 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
| smdavies99 wrote: |
What are the datatypes of these two fields in the DB? This can also have an effect?
What happens if you use a tool like db2cc, SQLServer Admin etc and manually issue the insert? using the values 1.51 and 1.59 (remove the coalesce)
What values are actually inserted? |
They are both Float datatype - we normally use Number (10,2)...
Don't think the table definition is the issue as depending on the order that the rows are inserted both columns can store the correct value.
I've could run the insert statements manually using SQL Developer but I am pretty sure it will work fine - maybe the next step would be to configure SQL Developer to use the same ODBC datasource as MB? |
|
| Back to top |
|
|
| koobz |
| Posted: Tue Mar 26, 2013 7:03 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
| koobz wrote: |
| the value for REGULARHRS in the last insert statement below, 1.49, gets rounded to 1. |
... when viewed in the database |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Mar 26, 2013 7:06 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
koobz: Two important tips for you
1. When taking a user trace, you need to use three command-line programs. mqsichangetrace to enable or disable the tracing.
mqsireadlog to read the trace as an XML file
mqsiformatlog to convert the XML file to a nice, human-readable format.
It looks as if you did not use mqsiformatlog.
2. When posting text output to this forum, *please* use [c o d e] tags to make the output easy to read. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Mar 27, 2013 2:16 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Looks to me like you are using Coalesce without having defined the type of the variable you are assigning the result to before hand.
What is likely happening is that on the first iteration the coalesce is activated it returns 0 and this is taken as an integer and fixes the type of the variable used. All subsequent results are then implicitly cast to the result variable type.... This is why it works when you use Coalesce(var, 0.00) because the implicit type is decimal.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| adubya |
| Posted: Wed Mar 27, 2013 2:55 am Post subject: |
|
|
Partisan
Joined: 25 Aug 2011
Posts: 377
Location: GU12, UK
|
| fjb_saper wrote: |
Looks to me like you are using Coalesce without having defined the type of the variable you are assigning the result to before hand.
What is likely happening is that on the first iteration the coalesce is activated it returns 0 and this is taken as an integer and fixes the type of the variable used. All subsequent results are then implicitly cast to the result variable type.... This is why it works when you use Coalesce(var, 0.00) because the implicit type is decimal.
|
If you look at the trace output though then that doesn't stack up. |
|
| Back to top |
|
|
| koobz |
| Posted: Wed Mar 27, 2013 7:38 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
| adubya wrote: |
| fjb_saper wrote: |
Looks to me like you are using Coalesce without having defined the type of the variable you are assigning the result to before hand.
What is likely happening is that on the first iteration the coalesce is activated it returns 0 and this is taken as an integer and fixes the type of the variable used. All subsequent results are then implicitly cast to the result variable type.... This is why it works when you use Coalesce(var, 0.00) because the implicit type is decimal.
|
If you look at the trace output though then that doesn't stack up. |
also in my code I am not assigning the value returned by COALESCE to a variable - it goes straight into the insert statement. |
|
| Back to top |
|
|
| koobz |
| Posted: Wed Mar 27, 2013 8:27 am Post subject: |
|
|
Novice
Joined: 26 Mar 2013
Posts: 11
|
| koobz wrote: |
Anyway I can get it to work by changing the code to use COALESCE(X,0.00)...
|
My mistake ... it works intermittently 
If I submit the same message 5 times using soapUI
| Code: |
<ns2:Transactions>
<ns2:Transaction>
...
<ns2:OvertimeHours>1.00</ns2:OvertimeHours>
...
</ns2:Transaction>
<ns2:Transaction>
...
<ns2:RegularHours>1.00</ns2:RegularHours>
....
</ns2:Transaction>
<ns2:Transaction>
...
<ns2:OvertimeHours>1.51</ns2:OvertimeHours>
...
</ns2:Transaction>
<ns2:Transaction>
...
<ns2:RegularHours>1.95</ns2:RegularHours>
....
</ns2:Transaction>
...
</ns2:Transactions> |
then I get 5 rows inserted in to the database for the 4th transaction, but two of them have the rounding error on the RegularHours, while three are OK.
Its seems the rounding error only ever occurs on values that didn't exist in the first xml Transaction entity and then only intermittently.
What's more the logs don't look like they are trustworthy.
I've clicked soapUI 5 times and its correctly led to 5 rows being inserted (per transaction) but the logs only show insert statements for the 3 rows without the error!
Furthermore when I made the 1st transaction
| Code: |
<ns2:Transaction>
...
<ns2:RegularHours/>
<ns2:OvertimeHours>1.00</ns2:OvertimeHours>
...
</ns2:Transaction>
|
e.g. with an empty tag for the null RegularHours
I was able to submit the message 10 times without any rounding error.
Therefore I don't see how it can be an error with the datasource itself, it seems like its the way MB is invoking it.
btw Kimbert - Thanks for the mqsiformatlog tip. If I use the code tags for posting log data is there anyway to bold/highlight the bits that seem significant?
I will have another go producing a log file and see if I can get it to definitively log the insert statements for both the correct and the rounded values. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Mar 27, 2013 8:50 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Quote: |
| If I use the code tags for posting log data is there anyway to bold/highlight the bits that seem significant? |
Not by using the normal tags. Which is strange, because they work inside a quote tag.
Best option is to use the code tags, then either
a) use chevrons or some other scheme >>>> to highlight the important lines
or
b) quote the lines separately below
The problem with not using code tags is that you get a variable-width font, and it makes any column-formatted output very hard to read. Code sections use a fixed-width font. |
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
