Issue
I am running the below query in oracle.
WITH
ta AS (
SELECT account_coid
,txn_id
,cbdev.cbzdt(effective_date) AS effective_date
,cbdev.cbchr(utl_raw.substr(txn_data, 113, 20)) AS CESG_amt
FROM bs_transaction
WHERE sub_type = 127469880)
SELECT
cast(ta.CESG_amt as DECIMAL (20,2)) AS cesg_amt
from ta
inner join ....
Here, i m getting the result (cesg_amt
) as -156.57
. But i need the result as -0000000000156.57
.
I need the leading zeros with -
retained (leading 0's and also the two digits after the decimal).
I have tried as to_char(ta.CESG_amt, '0000000000000.00') AS cesg_amt
in the query but of no use.
Can you please help me what needs to be done in the DECIMAL field to get the result as below.
Solution
You may use such a formatting :
select to_char(-156.57,'fm0000000000000D00','NLS_NUMERIC_CHARACTERS = ''.,''')
as Result
from dual;
RESULT
-----------------
-0000000000156.57
Answered By - Barbaros Özhan Answer Checked By - Candace Johnson (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.