| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| CAST Exception while DB call |
« View previous topic :: View next topic » |
| Author |
Message
|
| chaitu |
 Posted: Tue Aug 18, 2015 3:49 am Post subject: CAST Exception while DB call |
 |
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hello All,
I am calling a SQL DB 2012 from toolkit using passthru.
I am getting cast exception like
| Code: |
ExceptionList
RecoverableException
File:CHARACTER:F:\build\slot1\S900_P\src\DataFlowEngine\MessageServices\ImbDataFlowNode.cpp
Line:INTEGER:1155
Function:CHARACTER:ImbDataFlowNode::createExceptionList
Type:CHARACTER:ComIbmMQInputNode
Name:CHARACTER:WMStoPepBoys#FCMComposite_1_4
Label:CHARACTER:WMStoPepBoys.WMSIN
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Node throwing exception
Insert
Type:INTEGER:14
Text:CHARACTER:WMStoPepBoys.WMSIN
RecoverableException
File:CHARACTER:F:\build\slot1\S900_P\src\DataFlowEngine\SQLNodeLibrary\ImbComputeNode.cpp
Line:INTEGER:497
Function:CHARACTER:ImbComputeNode::evaluate
Type:CHARACTER:ComIbmComputeNode
Name:CHARACTER:WMStoPepBoys#FCMComposite_1_2
Label:CHARACTER:WMStoPepBoys.Compute
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2230
Text:CHARACTER:Caught exception and rethrowing
Insert
Type:INTEGER:14
Text:CHARACTER:WMStoPepBoys.Compute
RecoverableException
File:CHARACTER:F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:666
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ImbESQLManager
Name:CHARACTER:ImbESQLManager
Label:CHARACTER:ImbESQLManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.WMStoPepBoys_Compute.Main
Insert
Type:INTEGER:5
Text:CHARACTER:4.3
Insert
Type:INTEGER:5
Text:CHARACTER:CopyEntireMessage();
RecoverableException
File:CHARACTER:F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlRoutine.cpp
Line:INTEGER:1223
Function:CHARACTER:SqlRoutine::invoke
Type:CHARACTER:ImbESQLManager
Name:CHARACTER:ImbESQLManager
Label:CHARACTER:ImbESQLManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2934
Text:CHARACTER:Error occured in procedure
Insert
Type:INTEGER:5
Text:CHARACTER:CopyEntireMessage
RecoverableException
File:CHARACTER:F:\build\slot1\S900_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp
Line:INTEGER:666
Function:CHARACTER:SqlStatementGroup::execute
Type:CHARACTER:ImbESQLManager
Name:CHARACTER:ImbESQLManager
Label:CHARACTER:ImbESQLManager
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2488
Text:CHARACTER:Error detected, rethrowing
Insert
Type:INTEGER:5
Text:CHARACTER:.WMStoPepBoys_Compute.CopyEntireMessage
Insert
Type:INTEGER:5
Text:CHARACTER:5.2
Insert
Type:INTEGER:5
Text:CHARACTER:SET Environment.Variables.Order.records[ ] = SPECIFICPASSTHRU('SELECT O.LOTATTRIBUTE_LOTTABLE02,
O.LOTATTRIBUTE_LOTTABLE09,
STORER.ST_COMPANY,
STORER.ST_ADDRESS1,
STORER.ST_ADDRESS2,
STORER.ST_ADDRESS3,
STORER.ST_CITY,
STORER.ST_STATE,
STORER.ST_ZIP,
STORER.ST_COUNTRY,
O.PICKDETAIL_SKU,
SUM(O.PICKDETAIL_QTY) AS SUM_PICKDETAIL_QTY,
O.LOTATTRIBUTE_LOTTABLE08,
CONVERT(DECIMAL(25,6),(CASE WHEN LEN(RTRIM(LTRIM(O.LOTATTRIBUTE_LOTTABLE08))) = 0 THEN '0.00' ELSE O.LOTATTRIBUTE_LOTTABLE08 END)) * SUM(O.PICKDETAIL_QTY) AS ITEM_AMOUNT,
CAST(O.SKU_NOTES1 AS character(30)) AS NOTES1
FROM GEO66.VW_EIL_LOADLOADED_ORDERS O
LEFT JOIN GEO66.VW_EIL_STORER STORER ON STORER.ST_TITLE2 = O.LOTATTRIBUTE_LOTTABLE02 AND STORER.ST_ACTIVE = '1'
WHERE O.ORDERS_ORDERKEY IN (
SELECT LOADORDERDETAIL_SHIPMENTORDERID
FROM GEO66.VW_EIL_LOADLOADED_LOAD
WHERE KEY1 =' || KEY || '
)
Group by
O.LOTATTRIBUTE_LOTTABLE02,
O.LOTATTRIBUTE_LOTTABLE09,
O.LOTATTRIBUTE_LOTTABLE08,
STORER.ST_COMPANY,
STORER.ST_ADDRESS1,
STORER.ST_ADDRESS2,
STORER.ST_ADDRESS3,
STORER.ST_CITY,
STORER.ST_STATE,
STORER.ST_ZIP,
STORER.ST_COUNTRY,
O.PICKDETAIL_SKU,
CAST(O.SKU_NOTES1 AS character(30))', Database.WMSDBCONNECT);
CastException
File:CHARACTER:F:\build\slot1\S900_P\src\CommonServices\ImbDecimal.cpp
Line:INTEGER:539
Function:CHARACTER:ImbDecimal::ImbDecimal
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPmsgs
Severity:INTEGER:3
Number:INTEGER:2592
Text:CHARACTER:Invalid characters
Insert
Type:INTEGER:2
Text:CHARACTER:-1
Insert
Type:INTEGER:5
Text:CHARACTER:S22018
Insert
Type:INTEGER:5
Text:CHARACTER:1,804.86 |
I tried with float Numeric in place of DECIMAL(CONVERT(DECIMAL(25,5))
I hope its not taking 4 place before decimal(1,804.860000)
could any one please help me to figure it out.
Thanks in advance.
 |
|
| Back to top |
|
 |
| Vitor |
| Posted: Tue Aug 18, 2015 4:28 am Post subject: Re: CAST Exception while DB call |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| chaitu wrote: |
| I hope its not taking 4 place before decimal(1,804.860000) |
I hope you've properly accounted for that comma (,) in what's supposed to be a number. You know it's a thousands separator, I know it's a thousands separator, does the code know that......?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| chaitu |
| Posted: Wed Aug 19, 2015 1:24 am Post subject: |
|
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hi All,
I tried with Convert(DECIMAL(*,13)and with style also Convert(DECIMAL(25,13,1) but 
what else i have to do any or where i am wrong any suggestions please.
Thanks All. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 19, 2015 3:01 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
have you tried CAST with FORMAT?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| chaitu |
| Posted: Wed Aug 19, 2015 3:38 am Post subject: |
|
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hello All,
The code is
DECLARE KEY CHARACTER InputRoot.XMLNSC.Message.Event.KEY1;
SET Environment.Variables.Order.records[] = PASSTHRU('SELECT O.LOTATTRIBUTE_LOTTABLE02,
O.LOTATTRIBUTE_LOTTABLE09,
STORER.ST_COMPANY,
STORER.ST_ADDRESS1,
STORER.ST_ADDRESS2,
STORER.ST_ADDRESS3,
STORER.ST_CITY,
STORER.ST_STATE,
STORER.ST_ZIP,
STORER.ST_COUNTRY,
O.PICKDETAIL_SKU,
SUM(O.PICKDETAIL_QTY) AS SUM_PICKDETAIL_QTY,
O.LOTATTRIBUTE_LOTTABLE08,
CONVERT(DECIMAL(26,5),(CASE WHEN LEN(RTRIM(LTRIM(O.LOTATTRIBUTE_LOTTABLE08))) = 0 THEN ''0.00'' ELSE O.LOTATTRIBUTE_LOTTABLE08 END)) * SUM(O.PICKDETAIL_QTY) AS ITEM_AMOUNT,
CAST(O.SKU_NOTES1 AS char(30)) AS NOTES1
FROM GEO66.VW_EIL_LOADLOADED_ORDERS O
LEFT JOIN GEO66.VW_EIL_STORER STORER ON STORER.ST_TITLE2 = O.LOTATTRIBUTE_LOTTABLE02 AND STORER.ST_ACTIVE = ''1''
WHERE O.ORDERS_ORDERKEY IN (
SELECT LOADORDERDETAIL_SHIPMENTORDERID
FROM GEO66.VW_EIL_LOADLOADED_LOAD
WHERE KEY1 ='||KEY||'
)
Group by
O.LOTATTRIBUTE_LOTTABLE02,
O.LOTATTRIBUTE_LOTTABLE09,
O.LOTATTRIBUTE_LOTTABLE08,
STORER.ST_COMPANY,
STORER.ST_ADDRESS1,
STORER.ST_ADDRESS2,
STORER.ST_ADDRESS3,
STORER.ST_CITY,
STORER.ST_STATE,
STORER.ST_ZIP,
STORER.ST_COUNTRY,
O.PICKDETAIL_SKU,
CAST(O.SKU_NOTES1 AS char(30))' TO Database.DSN);
its a query given by client to retrive data from DB i used cast in place of convert returning DB error. this code is working fine for 32.90, 134.00 but returning casting error if the value is 4565.80000 to retrivew from DB.
any help is appreciated in advance. |
|
| Back to top |
|
|
| chaitu |
| Posted: Thu Aug 20, 2015 3:30 am Post subject: |
|
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hello All,
Could any one help on the below. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Aug 20, 2015 4:30 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Leave the raw return in environment and adjust when you go to the output root. Looks you probably need a round function on the DB to get a consistent number of decimals... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Aug 20, 2015 5:07 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
I'm confused.
It's complaining about bad characters in the SKU_NOTES1 field, not anything to do with the DECIMAL field.
Perhaps the CCSID of the data in the field was wrong when rows were inserted.
Perhaps you should include a CCSID when you do the cast to character ?
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| chaitu |
| Posted: Thu Aug 20, 2015 7:38 pm Post subject: |
|
|
Voyager
Joined: 15 Apr 2014
Posts: 89
|
Hello All,
Thanks for prompt reply,
records
LOTATTRIBUTE_LOTTABLE02:CHARACTER:xxxxxxxxxxx
LOTATTRIBUTE_LOTTABLE09:CHARACTER:xxxxxxxxx
ST_COMPANY:CHARACTER:xxxxxxxxxxxxx INC.(IMPORT)
ST_ADDRESS1:CHARACTER:xxxxxxxxxxx
ST_ADDRESS2:UNKNOWN:null
ST_ADDRESS3:UNKNOWN:null
ST_CITY:CHARACTER:xxxxxxx
ST_STATE:CHARACTER:CA
ST_ZIP:CHARACTER:90224
ST_COUNTRY:CHARACTER:
PICKDETAIL_SKU:CHARACTER:8957474
SUM_PICKDETAIL_QTY:DECIMAL:4.00
LOTATTRIBUTE_LOTTABLE08:CHARACTER:9.9
ITEM_AMOUNT:DECIMAL:39.60
NOTES1:CHARACTER:4PC xxxxxxxxxxxxxxxxxxxxx
records
LOTATTRIBUTE_LOTTABLE02:CHARACTER:xxxxxxxxxx
LOTATTRIBUTE_LOTTABLE09:CHARACTER:xxxxxxxxxx
ST_COMPANY:CHARACTER:xxxxxxxxxxxxxxxxx (IMPORT)
ST_ADDRESS1:CHARACTER:4355 BRICKELL ST
ST_ADDRESS2:CHARACTER:
ST_ADDRESS3:UNKNOWN:null
ST_CITY:CHARACTER:xxxxxx
ST_STATE:CHARACTER:CA
ST_ZIP:CHARACTER:91761
ST_COUNTRY:CHARACTER:
PICKDETAIL_SKU:CHARACTER:0538918
SUM_PICKDETAIL_QTY:DECIMAL:18.00
LOTATTRIBUTE_LOTTABLE08:CHARACTER:100.27
ITEM_AMOUNT
I am getting the response from DB like above and the error is at ITEM AMOUNt field in second record and the error is in below posts
(Insert
Type:INTEGER:5
Text:CHARACTER:1,804.86.)
ITEM_AMOUNT value is 1804.860000 in DB.
Any Suggestions Please.
Thanks |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
