Query to change packed decimal to decimal



IBM's flagship relational database management system

Query to change packed decimal to decimal

Postby vishnusrini » Tue May 17, 2011 4:59 pm

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

Postby dick scherrer » Wed May 18, 2011 3:09 am

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.
User avatar
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

Postby vishnusrini » Wed May 18, 2011 10:31 am

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

Postby BillyBoyo » Wed May 18, 2011 11:48 am

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

Postby vishnusrini » Wed May 18, 2011 12:40 pm

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

Postby NicC » Wed May 18, 2011 2:17 pm

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

Postby GuyC » Wed May 18, 2011 3:06 pm

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

Postby enrico-sorichetti » Wed May 18, 2011 7:35 pm

Everybody should have understood by now ...
that anything that is not a user program goes under the name of JCL around here :mrgreen:
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: 2994
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

Postby dick scherrer » Wed May 18, 2011 11:30 pm

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
User avatar
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

Postby vishnusrini » Thu May 19, 2011 10:19 am

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