|
View previous topic :: View next topic
|
| Author |
Message |
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
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 |
|
 |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| Code: |
case when AMOUNT = 0 then '0.00'
when AMOUNT < 0 then '-'||strip(char(AMOUNT))
else '+'||strip(char(AMOUNT))
end |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2018
Location: USA
|
|
|
|
| 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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
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 |
|
|
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
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 |
|
|
sumannath
New User
Joined: 20 Mar 2017
Posts: 8
Location: India
|
|
|
|
You can try this as well:
| Code: |
SELECT TO_CHAR(AMOUNT/100,'S0000000000000000.00')
FROM TABLE
|
N.B.: Untested |
|
| Back to top |
|
|
jaguyria
New User
Joined: 15 Feb 2022
Posts: 21
Location: Portugal
|
|
|
|
Hello sumannath,
I didn't know the function TO_CHAR.
I've already opted for other solution, but in the future I'll use it. , thanks a lot !
This works, but in my case no need to / 100 
Kind regards |
|
| Back to top |
|
|
|
|
