IBM Mainframe Forum

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

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.

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

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

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.

Actually we are using this query directly in JCL not in program. So it is not possible to use EDIT field.

In JCL - wow. Didn't know that JCL (JOB CONTROL language) knew anything about packed, or any other type of decimal. Do tell us.

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.

Everybody should have understood by now ...
that anything that is not a user program goes under the name of JCL around here

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

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.