IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to display the leading zeros of a decimal data in DB2?


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
jaguyria

New User


Joined: 15 Feb 2022
Posts: 14
Location: Portugal

PostPosted: Thu Jun 09, 2022 10:03 pm
Reply with quote

Hello,

I have a field "AMOUNT" defined in my DB2 table as PIC S9(16)V9(2).

I need to display this field in a query with the sign, the leading zeros and the decimal separator (.) when I display it in the query DB2.

For example:
AMOUNT ---------------------------------- AMOUNT displayed in Db2 (desired)
686123.24 must be represented as---> +0000000000686123.24

For the sign and leading zeros I think it's ok doing:
SELECT CASE WHEN AMOUNT < 0 '-' ELSE '+' END || DIGITS(AMOUNT)
result:+000000000068612324

Note that it's missing the decimal separator, the dot (.)

Someone have ideas how can I do this without missing the decimal separator?

Thanks in advance!
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2905
Location: NYC,USA

PostPosted: Thu Jun 09, 2022 11:13 pm
Reply with quote

Code:
case when AMOUNT = 0 then '0.00'
     when AMOUNT < 0 then '-'||strip(char(AMOUNT))
  else '+'||strip(char(AMOUNT))
end 
Back to top
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 1533

PostPosted: Fri Jun 10, 2022 12:02 am
Reply with quote

jaguyria wrote:
I have a field "AMOUNT" defined in my DB2 table as PIC S9(16)V9(2).


The field in DB2 table cannot be defined as PIC S9(16)V9(2), it is a COBOL field definition used somewhere in COBOL code, with no direct relation to any DB2 field, which looks like this: NUMERIC(16,2)

If you need to change the field presentation in your COBOL program, you must change the COBOL definition, but not DB2 field.

If you moved your value received from DB2 to the COBOL field PIC SZ(16).V9(2), then it would look like you want to see it.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2905
Location: NYC,USA

PostPosted: Fri Jun 10, 2022 1:28 am
Reply with quote

This is a simple question and has nothing to do with COBOL as far as I understand the ask. TS just trying to represent simpler way to ask the question using dclgen field and in Db2 it’s indeed defined as dec(18,2).
Back to top
View user's profile Send private message
jaguyria

New User


Joined: 15 Feb 2022
Posts: 14
Location: Portugal

PostPosted: Mon Jun 20, 2022 5:10 pm
Reply with quote

Hello,

@sergeyken yes, I should have said that I have a field "AMOUNT" defined in my DB2 table as DECIMAL(18,2) NOT NULL as @Rohit said.
The definition PIC S9(16)V9(2) is for the cobol declaration of the variable of table in use.

@Rohit thanks for your suggestion, but I've tried strip(char(AMOUNT)) and it gives:+686123.24 without leading zeros (as I need):
+0000000000686123.24

Any other suggestion?
Back to top
View user's profile Send private message
jaguyria

New User


Joined: 15 Feb 2022
Posts: 14
Location: Portugal

PostPosted: Mon Jun 20, 2022 7:49 pm
Reply with quote

Hello,

This works for me

Code:
SELECT CASE WHEN AMOUNT < 0 '-' ELSE '+' END ||
SUBSTR(DIGITS(AMOUNT),1,16)
|| '.'
|| SUBSTR(DIGITS(AMOUNT),17,2)


result:+0000000000686123.24

Thank you everyone!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts String has hex character need to conv... COBOL Programming 3
No new posts Setting CA7 user variable from data s... CA Products 7
No new posts pass data as symbolic parameter from ... CLIST & REXX 2
No new posts Assembler: Set Program Mask for decim... PL/I & Assembler 4
No new posts Decompress data from JMR log file Compuware & Other Tools 4
Search our Forums:

Back to Top