## Query to change packed decimal to decimal

IBM's flagship relational database management system

### Query to change packed decimal to decimal

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
vishnusrini

Posts: 32
Joined: Wed Jul 07, 2010 2:55 pm
Location: India,bangalore
Has thanked: 0 time
Been thanked: 0 time

### Re: Need query to change packed decimal to decimal

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.
Hope this helps,
d.sch.

dick scherrer
Global moderator

Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

### Re: Need query to change packed decimal to decimal

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

Posts: 32
Joined: Wed Jul 07, 2010 2:55 pm
Location: India,bangalore
Has thanked: 0 time
Been thanked: 0 time

### Re: Need query to change packed decimal to decimal

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.
BillyBoyo
Global moderator

Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

### Re: Need query to change packed decimal to decimal

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

Posts: 32
Joined: Wed Jul 07, 2010 2:55 pm
Location: India,bangalore
Has thanked: 0 time
Been thanked: 0 time

### Re: Need query to change packed decimal to decimal

In JCL - wow. Didn't know that JCL (JOB CONTROL language) knew anything about packed, or any other type of decimal. Do tell us.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator

Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

### Re: Need query to change packed decimal to decimal

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.
I can explain it to you, but i can not understand it for you.
GuyC

Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

### Re: Need query to change packed decimal to decimal

Everybody should have understood by now ...
that anything that is not a user program goes under the name of JCL around here
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator

Posts: 2999
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

### Re: Need query to change packed decimal to decimal

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

dick scherrer
Global moderator

Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

### Re: Need query to change packed decimal to decimal

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.
vishnusrini

Posts: 32
Joined: Wed Jul 07, 2010 2:55 pm
Location: India,bangalore
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

• Related topics
Replies
Views
Last post