| Author |
Message
|
| rasol |
 Posted: Wed Aug 16, 2006 12:52 pm Post subject: Select sum |
 |
|
Novice
Joined: 08 Aug 2006
Posts: 14
|
Hi All,
I want to acculmate the amount of each item from a XML message.
Here is the code and it works fine
[code]
WHILE (Count <= Environment.Variables.TotalRecords) DO
SET Environment.Variables.Amount = CAST(InputRoot.MRM.ns:cob_details.details:cob_details[Count].details:AMOUNT AS DECIMAL);
SET Totalsum = Totalsum + Environment.Variables.Amount;
SET Count = Count + 1;
END WHILE;
[/code]
but for efficiency, I used SELECT SUM and nothin is return. I cannot see Environment.Variables.Total in debug mode.
[code]
SET Environment.Variables.Total = (SELECT SUM(CAST(I.AMOUNT AS DECIMAL)) FROM InputRoot.MRM.ns:cob_details.*[] AS I);
[/code]
Any suggestion |
|
| Back to top |
|
 |
| JT |
| Posted: Wed Aug 16, 2006 2:45 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
| rasol |
| Posted: Wed Aug 16, 2006 3:52 pm Post subject: |
|
|
Novice
Joined: 08 Aug 2006
Posts: 14
|
| I looked into that thread and I coded the same but I cannot see Environment.Variables.Total field after the execution of SELECT statement in debug mode. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 16, 2006 3:58 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
How do you handle one of the values being null?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rasol |
| Posted: Wed Aug 16, 2006 4:01 pm Post subject: |
|
|
Novice
Joined: 08 Aug 2006
Posts: 14
|
| none of the values are null. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 16, 2006 4:06 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| rasol wrote: |
| none of the values are null. |
You mean to say that none of the CASTS (I.AMOUNT to decimal) will ever return a null value? How can you garantee this? You might want to use coalesce...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| rasol |
| Posted: Wed Aug 16, 2006 10:17 pm Post subject: |
|
|
Novice
Joined: 08 Aug 2006
Posts: 14
|
The message comes from SAP system and we have handled AMOUNT field upfront in SAP system .So AMOUNT will not have null values when it comes to WBI.
All I have to do is to accumulate the sum of all the occurance from a XML message. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Aug 17, 2006 2:40 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| rasol wrote: |
The message comes from SAP system and we have handled AMOUNT field upfront in SAP system .So AMOUNT will not have null values when it comes to WBI.
|
I would not trust the last part of this statement that much. The question is could you ever have spaces instead of a numeric value? And if you always have a numeric value could there be instances where your CAST as DECIMAL would produce a null value?
The return from your select seems to be familiar with at least one of the result values being null which would make the result be null.
Enjoy 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
