Page 1 of 2

Query to change packed decimal to decimal

PostPosted: Tue May 17, 2011 4:59 pm
by vishnusrini
Hi all,

I need DB2 query to convert Packed decimal to signed decimal value.

For example,
Packed decimal --> +999999999999999999.99

I tried this below query, but i can only see -ve sign and for +ve sign it is coming as space. I need +ve sign also in the output file.

SELECT CHAR(CAST(AMOUNT_A AS DECIMAL(18,2)))

Output that i got for thes query :
Packed decimal --> 999999999999999999.99 (if the value is +ve)
Packed decimal --> -999999999999999999.99(if the value is -ve)

But i need output in below format,
Packed decimal --> +999999999999999999.99 (if the value is +ve)
Packed decimal --> -999999999999999999.99(if the value is -ve)


Thanks & regards,
VishnuSrinivasan.M
emai: vishnusrini@gmail.com

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 3:09 am
by dick scherrer
Hello,

The "packed decimal' values posted are NOT packed decimal.

Please post a more complete example of what you really "have" and what you "want".

Hint: Packed decimal numbers do not have a visable sign. The sign is a hex C, D, or F and is stored in the low-order nibble of the low-order byte.

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 10:31 am
by vishnusrini
Example 1:
~~~~~~~~
Packed Decimal ---> ........rae
hexadecimal value:
0000000199
000000009C

After excuting above query we got below output,
Query output ---> 0000000000000109.99

But excepted output ---> +0000000000000109.99

Example 2:
~~~~~~~~~
Packed Decimal ---> .........ì(
hexadecimal value:
0000000054
000000168D

After excuting above query we got below output,
Query output ---> -0000000000001065.84

Excepted output ---> -0000000000001065.84

I think above example will explain you that we are not gitting '+' sign when we run that query, but we need '+' also.

Thanks & Regards,
VishnuSrinivasan.M
email:vishnusrini@gmail.com

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 11:48 am
by BillyBoyo
vishnusrini wrote:Example 1:
~~~~~~~~
Packed Decimal ---> ........rae
hexadecimal value:
0000000199
000000009C

After excuting above query we got below output,
Query output ---> 0000000000000109.99

But excepted output ---> +0000000000000109.99

Example 2:
~~~~~~~~~
Packed Decimal ---> .........ì(
hexadecimal value:
0000000054
000000168D

After excuting above query we got below output,
Query output ---> -0000000000001065.84

Excepted output ---> -0000000000001065.84

I think above example will explain you that we are not gitting '+' sign when we run that query, but we need '+' also.

Thanks & Regards,
VishnuSrinivasan.M
email:vishnusrini@gmail.com


0000000199
000000009C
                *
0000000054
000000168D
                *


The "C" is the positive sign and the "D" is the negative sign. If you are saying you want to see a "+" or a "-", that is a different thing. Look at Numeric Edited fields.

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 12:40 pm
by vishnusrini
Actually we are using this query directly in JCL not in program. So it is not possible to use EDIT field.

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 2:17 pm
by NicC
In JCL - wow. Didn't know that JCL (JOB CONTROL language) knew anything about packed, or any other type of decimal. Do tell us.

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 3:06 pm
by GuyC
NicC wrote:In JCL - wow. Didn't know that JCL (JOB CONTROL language) knew anything about packed, or any other type of decimal. Do tell us.

JCL that knows DB2 SQL queries is also some extension I haven't heard of.

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 7:35 pm
by enrico-sorichetti
Everybody should have understood by now ...
that anything that is not a user program goes under the name of JCL around here :mrgreen:

Re: Need query to change packed decimal to decimal

PostPosted: Wed May 18, 2011 11:30 pm
by dick scherrer
Hello,

After excuting above query we got below output,
Query output ---> 0000000000000109.99

But excepted output ---> +0000000000000109.99
Suggest the expectation be changed. . . Why does someone (anyone?) believe the plus-sign is needed in the output. . .

d

Re: Need query to change packed decimal to decimal

PostPosted: Thu May 19, 2011 10:19 am
by vishnusrini
As per client requirement we need that sign in the output file. :(

That too we should not write a new program for this change. They want us to do it in JCL use DB2. :(