How to add decimal places in to my oracle SQL query result

April 9, 2024

I have the following query

select oc.card_number,
       case opt.oper_type
       when 'OPTP0000' then '05-PURCHASE' 
       when 'OPTP0020' then '06-CREDIT VOUCHER' 
       else 'other' end as Transaction_Type,
       opt.oper_date as "TRANSACTION_DATE", 
       op.account_number, 
       case vf.sttl_currency
       when '840' then 'USD'
       end as "SOURCE_AMOUNT_CURRENCY", 
       case opt.oper_type
       when 'OPTP0000' then  -vf.sttl_amount
       else  vf.sttl_amount
       end as "SOURCE_AMOUNT",
       op.auth_code, 
       opt.mcc, 
       opt.terminal_number as "TERMINAL_ID", 
       vf.pos_terminal_cap, 
       vf.pos_entry_mode,
       opt.part_key as "CAPTURE_DATE", 
       opt.oper_amount as "TRANSACTION_AMOUNT",
       opt.oper_currency as "TRANSACTION_CURRENCY",
       opt.merchant_name,
       COALESCE(cc.visa_country_code,opt.merchant_country) merchant_country,
       opt.clearing_sequence_num, 
       opt.clearing_sequence_count, 
       opt.oper_type
FROM   opr_operation opt 
       INNER JOIN opr_participant op
       ON op.oper_id = opt.id
       INNER JOIN opr_card oc
       ON oc.oper_id = opt.id
       INNER JOIN vis_fin_message vf
       ON     opt.id = vf.id
       INNER JOIN  com_country cc
       ON cc.code= opt.merchant_country 
          AND op.card_id = vf.card_id
WHERE  opt.clearing_sequence_num > 1
AND    opt.part_key >= DATE '2024-03-01'
AND    opt.msg_type IN ('MSGTPAMC', 'MSGTPACC')
AND    op.participant_type = 'PRTYISS'

Which gives the following output:

"CARD_NUMBER"   "TRANSACTION_TYPE"  "TRANSACTION_DATE"  "ACCOUNT_NUMBER"    "SOURCE_AMOUNT_CURRENCY"    "SOURCE_AMOUNT" "AUTH_CODE" "MCC"   "TERMINAL_ID"   "POS_TERMINAL_CAP"  "POS_ENTRY_MODE"    "CAPTURE_DATE"  "TRANSACTION_AMOUNT"    "TRANSACTION_CURRENCY"  "MERCHANT_NAME" "MERCHANT_COUNTRY"  "CLEARING_SEQUENCE_NUM" "CLEARING_SEQUENCE_COUNT"   "OPER_TYPE"
"1234567891234567"  "05-PURCHASE"   25-MAR-24   "004000485011"  "USD"   -2020   "718515"    "5734"  "TERMID01"  "9" "01"    27-MAR-24   2020    "060"   "ACQUIRER NAME            " "BM"    2   2   "OPTP0000"

But, it is possible to get column F – ‘SOURCE_AMOUNT’ to return the value with 2 decimal places?

So in the example above, -2020 should be displayed as -20.20

I did try to do to_char as below but it is not correct and doesnt work

 case opt.oper_type
       when 'OPTP0000' then to_char (coalesce (-vf.sttl_amount,0)999999.99)
       else  vf.sttl_amount
       end as "SOURCE_AMOUNT",

Any help would be great please.

>Solution :

Apply TO_CHAR with appropriate format model to the whole CASE expression, e.g.

   TO_CHAR (
      CASE opt.oper_type
         WHEN 'OPTP0000' THEN COALESCE (-vf.sttl_amount, 0)
         ELSE vf.sttl_amount
      END,
      '999G990D00') AS SOURCE_AMOUNT

This will apply thousands separator and decimal point character to that value. Check documentation for other possibilities.

Although not related to your question: try not to use double quotes in Oracle; in vast majority of cases they aren’t needed. As you used them as column/expression aliases and all of them are in capital letters with underline character between words, there’s no difference among source_amount, SOURCE_AMOUNT, "SOURCE_AMOUNT" and similar.