| Author |
Message
|
| m.schneider |
 Posted: Tue Jul 17, 2007 12:15 am Post subject: How to assign a SQL decimal value to an ESQL decimal |
 |
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Hello,
I'm trying to assign an SQL decimal to an ESQL decimal, but it doesn't work out. I receive the following error-message.
( TMBROKER.EXEGRP1 ) Fehler bei der Konvertierung der Zeichenfolge ''50,000000'' in einen Dezimalwert. Status = ''-1'' ''S22018'' '0' ''
Es wurde versucht, eine Zeichenfolge in einen Dezimalwert zu konvertieren, die Zeichenfolge hatte jedoch das falsche Format. Die Zeichenfolge muss das Format [<Vorzeichen>]<Ziffern>[.<Ziffern>][[<Vorzeichen>]E<Ziffern>] haben. Hierbei kann für <Vorzeichen> '+' oder '-' angegeben werden, und die <Ziffern> können eine oder mehrere Dezimalziffern (0-9) sein. Die Groß-/Kleinschreibung für E spielt keine Rolle. Dieser Zeichenfolge kann können beliebig viele Leerzeichen voran- oder nachgestellt sein. Innerhalb des angegebenen Formats dürfen jedoch keine Leerzeichen stehen. Die maximale Genauigkeit besteht aus 24 Ziffern, und der höchste Exponent darf 999.999.999 sein.
Meaning there has been an error during the conversion from 50,000 to a decimal value.
Here is coulum definition and ESQL code:
COMISSION_EURO DECIMAL(10,6),
DECLARE tmp_comissionEuro DECIMAL;
Set tmp_comissionEuro =THE ( SELECT ITEM C.COMISSION_EURO
FROM Database.CUSTOMER as C
);
Can anyone help? |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Jul 17, 2007 12:42 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
The data will be coming back from the database as CHARACTERs, so you will need to do a CAST to DECIMAL with a FORMAT clause, specifying a "decsep" (decimal separator) of ',' (comma).
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 |
|
|
| m.schneider |
| Posted: Tue Jul 17, 2007 2:49 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Thanks for your reply, but it still won't work.
DECLARE source CHARACTER;
DECLARE pattern CHARACTER '0000,000000';
DECLARE tmp_comissionEuro DECIMAL;
Set tmp_comissionEuro = CAST(
THE ( SELECT ITEM C.COMISSION_EURO
FROM Database.CUSTOMER as C
)
AS DECIMAL FORMAT pattern);
I receive the same error like posted before. |
|
| Back to top |
|
|
| m.schneider |
| Posted: Tue Jul 17, 2007 3:19 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
DECLARE pattern CHARACTER '-###0,000000:decsep=,';
DECLARE pattern CHARACTER '-###0.000000:decsep=,';
also didn't work out |
|
| Back to top |
|
|
| m.schneider |
| Posted: Fri Jul 20, 2007 12:22 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
I don't get this thing to work, .... I tried a workaround with
Set Environment.Variables.DBResult.Results[] =
( SELECT ITEM C.COMISSION_EURO
FROM Database.CUSTOMER as C );
But I get also an error during the assignment.
Can anyone help? |
|
| Back to top |
|
|
| m.schneider |
| Posted: Mon Jul 23, 2007 11:56 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Does anyone have a solution? I need this very urgent!!!
Thanks |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jul 24, 2007 2:05 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| m.schneider wrote: |
Does anyone have a solution? I need this very urgent!!!
Thanks |
Did you even read the posts. What happens when you introduce the decimal separator into your format??? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| m.schneider |
| Posted: Tue Jul 24, 2007 11:18 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Here is the solution:
SET Environment.Variables.Result = PASSTHRU ('SELECT CHAR(C.COMISSION_EURO)
FROM Database.CUSTOMER
TO Database.NORDLB);
SET tmp_comissionEuro = CAST (Environment.Variables.Result."1" AS DECIMAL);
@fjb_saper
I already tried the decsep
| Quote: |
Posted: Tue Jul 17, 2007 3:19 am Post subject:
DECLARE pattern CHARACTER '-###0,000000:decsep=,';
DECLARE pattern CHARACTER '-###0.000000:decsep=,';
also didn't work out
|
or did I get you wrong? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Jul 25, 2007 2:44 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| m.schneider wrote: |
Here is the solution:
SET Environment.Variables.Result = PASSTHRU ('SELECT CHAR(C.COMISSION_EURO)
FROM Database.CUSTOMER
TO Database.NORDLB);
SET tmp_comissionEuro = CAST (Environment.Variables.Result."1" AS DECIMAL);
@fjb_saper
I already tried the decsep
| Quote: |
Posted: Tue Jul 17, 2007 3:19 am Post subject:
DECLARE pattern CHARACTER '-###0,000000:decsep=,';
DECLARE pattern CHARACTER '-###0.000000:decsep=,';
also didn't work out
|
or did I get you wrong? |
Glad you could solve it.
From your post the format of the char variable was unclear. Did you get the char output with a decimal point or a decimal comma?
The decsep format declaration is supposed to match the way the data looks and should not match the 'locale' or whatever has been defined for the broker...
As well I am not sure I fully understood the way to define the format.
The assignee of decsep might need to be a string variable:
| Code: |
declare mydecsep character ',';
declare pattern character '-###0.000000:decsep=mydecsep'; |
but you better check it up in the manual, where one of the examples shows it as you defined it...
Then again you had 2 definitions of pattern in it... which certainly confused me...
| Quote: |
>>-| subpattern |--+-------------------+------------------------>
'-;--| subpattern |-'
>--+-------------------+--+-----------------+------------------><
'-:groupsep=--chars-' '-:decsep=--chars-'
subpattern
|--+-------+--digits--+---------+--+---------------+------------>
'-chars-' '-.digits-' '-+-e-+--digits-'
'-E-'
>--+-------+----------------------------------------------------|
'-chars-' |
Manual definition example of a pattern from the infocenter:
| Quote: |
+###,##0.00;-###,###,##0.00:groupsep='':decsep=,
|
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| m.schneider |
| Posted: Wed Jul 25, 2007 3:55 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Thanks for your reply
I tried the pattern one by one  not at the same time.
But in my opinion there's a lack of information, examples from IBM, ...
I would really appreciate an Application Programmer Redbook for the Message Broker. |
|
| Back to top |
|
|
| Yaroslav |
| Posted: Wed Oct 22, 2008 11:29 pm Post subject: |
|
|
Novice
Joined: 28 Sep 2006
Posts: 12
Location: Russia
|
Hello, m.schneider!
I think my answer is out of date for you, but it must be helpful for others.
I had the same error S22018 in my environment:
OS - Windows Server 2003
WMB 6.1
IBM DB2 v8.1.15 for Windows
In my case, the problem with comma decimal separator was solved by adding following workaround to ODBC data source in file db2cli.ini
[YOUDB]
PATCH2=15
DBALIAS=YOUDB
_________________
IBM Certified Solution Designer WebSphere MQ V6.0
IBM Certified Solution Developer WebSphere Message Broker V6.0 |
|
| Back to top |
|
|
| Zeleon |
| Posted: Fri May 07, 2010 10:54 am Post subject: |
|
|
Newbie
Joined: 07 May 2010
Posts: 2
|
Hi Yaroslav.
| Quote: |
OS - Windows Server 2003
WMB 6.1
IBM DB2 v8.1.15 for Windows
In my case, the problem with comma decimal separator was solved by adding following workaround to ODBC data source in file db2cli.ini
[YOUDB]
PATCH2=15
DBALIAS=YOUDB |
Do you have any more details to what this change actually does? |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Fri May 07, 2010 10:50 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Why not do a Google for
There are lots of differing views on this item.
_________________
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 |
|
|
| Zeleon |
| Posted: Sun May 09, 2010 11:15 am Post subject: |
|
|
Newbie
Joined: 07 May 2010
Posts: 2
|
| I did and found what I was looking for. PATCH2=15 did the trick for me. |
|
| Back to top |
|
|
|
|
