| Author |
Message
|
| mqmaniac |
 Posted: Wed Sep 06, 2006 6:53 am Post subject: CASTING Exception in 2.1 to V5.0 Broker Migration |
 |
|
Master
Joined: 27 Dec 2005
Posts: 201
|
Hello everyone..
We are migrating Message Broker 2.1 to V5.o and have the following Issue.
The following piece of code works fine in 2.1 version..BUT when the flow has been migrated to V5.0 gives too many exceptions on the CASTING operation..
ESQL Code:
DECLARE myCurrentTotalAmount FLOAT;
DECLARE myDecimal DECIMAL;
SET myCurrentTotalAmount = CAST("InputBody"."CURRENT_TOTAL_PAYMENT_AMOUNT" AS FLOAT);
SET myDecimal = CAST(myCurrentTotalAmount AS DECIMAL(9,2));
SET myTemp = CAST(SUBSTRING(CAST(myDecimal AS CHAR) FROM 1 FOR 7) AS INT);
The above code works fine in 2.1 Version of the broker..BUT in V5.0 It throws th following exceptions/Errors..Please help!!!
ERROR DETECTED WHILST EXECUTING THE SQL STATEMENT 'SET myTemp = CAST(SUBSTRING(CAST(myDecimal AS CHARACTER) FROM 1 FOR 7) AS INTEGER);'. : ImbRdlStatementGroup(158)
8.13.21 STC08756 +BIP2521E NH1BRK EG1 40 (.SDPT_MAP..NQ.RS.main, 148.14)
: ERROR CASTING A VALUE FROM CHARACTER TO INTEGER. : ImbRdlTypeCast(20
269 4)
08.13.21 STC08756 +BIP2595E NH1BRK EG1 40 ERROR CASTING CHARACTER STRING '2808.40' TO AN INTEGER. : ImbUtility(304)
I have searched for documentation for any code changes..Did not find any!!!Pls Help!!! |
|
| Back to top |
|
 |
| msukup |
| Posted: Wed Sep 06, 2006 7:51 am Post subject: |
|
|
Acolyte
Joined: 11 Feb 2002
Posts: 56
|
In migrating from 2.1 to 5.0, we also noticed some subtle changes in the decimal data type that, unfortunately, require code changes (just what you wanted to hear, i'm sure). Since you have to make changes anyways, investigate using the round function rather than casting to char and substringing out your integer.
I recall seeing a support pack in the spring that gave migration advice for detecting and dealing with decimal changes in migrating, but it doesn't seem to be on the support pack site anymore. |
|
| Back to top |
|
|
| mqmaniac |
| Posted: Wed Sep 06, 2006 12:23 pm Post subject: |
|
|
Master
Joined: 27 Dec 2005
Posts: 201
|
I see that the CAST in following steps:
Input-->float-->decimal-->character-->Integer.
I see the input in debug mode as 0002808.04
Did anyone encounter same kind of scenario..
Is there any simple way of CASTing instead of involving so many steps!!! |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Sep 06, 2006 2:13 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Firstly, as you imply, what you are doing is currently very inefficient.
Secondly, from the names of your fields it looks like you are dealing with money values, in which case FLOAT is NOT the right datatype to cast to as it is inherently inacurate and should only be used for inexact values. DECIMAL is the correct datatype for monetary values.
Thirdly, I would therefore CAST the initial value directly to DECIMAL (maybe with a DEFAULT clause if the cast could ever fail), then I would CAST the DECIMAL to an INTEGER which will cause the DECIMAL to be rounded to the nearest INTEGER value (with bankers rounding).
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| mqmaniac |
| Posted: Thu Sep 07, 2006 5:29 am Post subject: |
|
|
Master
Joined: 27 Dec 2005
Posts: 201
|
Thanks a Lot MGK for your Info..
This is what I tried:
The input was 0001208.48
SET myCurrentTotalAmount = CAST("InputBody"."CURRENT_TOTAL_PAYMENT_AMOUNT" AS FLOAT);
Value in Debug :1.5501530654897602E16
SET myDecimal = CAST(myCurrentTotalAmount AS DECIMAL(9,2));
Value in Debug :1208.58
SET temp =CAST(myDecimal AS CHAR);
Value in Debug:1208.58
SET temp1 =SUBSTRING(temp FROM 1 FOR 7);
Value in Debug:1208.58
SET myTemp =CAST(temp1 as INT);
This statement ends up in exceptions,My question is Why is 2.1 Broker able to process this statement and V5.0 unable to process this statement..
Can you pls tell me what need to be changed in the Code?????
Last edited by mqmaniac on Thu Sep 07, 2006 5:49 am; edited 1 time in total |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Sep 07, 2006 5:34 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Please read mgk's message again.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mqmaniac |
| Posted: Thu Sep 07, 2006 6:46 am Post subject: |
|
|
Master
Joined: 27 Dec 2005
Posts: 201
|
Thanks Jeff/Mgk..
Finally I made the floowing change...
SET outputAmount=SUBSTRING("InputBody"."CURRENT_TOTAL_PAYMENT_AMOUNT" FROM 1 FOR 7)||SUBSTRING("InputBody"."CURRENT_TOTAL_PAYMENT_AMOUNT" FROM 9 FOR 2);
..and It worked..I hope the Code written earlier was done bluntly!!! |
|
| Back to top |
|
|
|
|
