By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table zmt (amount, t_type) as
select 123456789.92, 'CREDIT' from dual;
1 rows affected
create table po (msgtosend_0182) as
select to_clob('{"amount":1.2345678992E8,"notes":"Nomina a Martin","transactionDetails":{"transactionChannelId":"_ach_enviada"}}') from dual;
1 rows affected
alter session set nls_numeric_characters='.,'
select ZMT.AMOUNT from zmt;
| AMOUNT |
|---|
| 123456789.92 |
select TO_CHAR(REPLACE(ZMT.AMOUNT, ',', '.')) from zmt;
| TO_CHAR(REPLACE(ZMT.AMOUNT,',','.')) |
|---|
| 123456789.92 |
select JSON_VALUE(PO.MSGTOSEND_0182, '$.amount') from po;
| JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT') |
|---|
| 123456789.92 |
select JSON_VALUE(PO.MSGTOSEND_0182, '$.amount' returning number) from po;
| JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT'RETURNINGNUMBER) |
|---|
| 123456789.92 |
select TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182, '$.amount'), ',', '.')) from po;
| TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT'),',','.')) |
|---|
| 123456789.92 |
select *
from zmt join po
on TO_CHAR(REPLACE(ZMT.AMOUNT, ',', '.')) = TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182, '$.amount'), ',', '.'))
| AMOUNT | T_TYPE | MSGTOSEND_0182 |
|---|---|---|
| 123456789.92 | CREDIT | {"amount":1.2345678992E8,"notes":"Nomina a Martin","transactionDetails":{"transactionChannelId":"_ach_enviada"}} |
select *
from zmt join po
on ZMT.AMOUNT = JSON_VALUE(PO.MSGTOSEND_0182, '$.amount' RETURNING NUMBER)
| AMOUNT | T_TYPE | MSGTOSEND_0182 |
|---|---|---|
| 123456789.92 | CREDIT | {"amount":1.2345678992E8,"notes":"Nomina a Martin","transactionDetails":{"transactionChannelId":"_ach_enviada"}} |
alter session set nls_numeric_characters=',.'
select ZMT.AMOUNT from zmt;
| AMOUNT |
|---|
| 123456789,92 |
select TO_CHAR(REPLACE(ZMT.AMOUNT, ',', '.')) from zmt;
| TO_CHAR(REPLACE(ZMT.AMOUNT,',','.')) |
|---|
| 123456789.92 |
select JSON_VALUE(PO.MSGTOSEND_0182, '$.amount') from po;
| JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT') |
|---|
| 123456789.92 |
select JSON_VALUE(PO.MSGTOSEND_0182, '$.amount' returning number) from po;
| JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT'RETURNINGNUMBER) |
|---|
| 123456789,92 |
select TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182, '$.amount'), ',', '.')) from po;
| TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182,'$.AMOUNT'),',','.')) |
|---|
| 123456789.92 |
select *
from zmt join po
on TO_CHAR(REPLACE(ZMT.AMOUNT, ',', '.')) = TO_CHAR(REPLACE(JSON_VALUE(PO.MSGTOSEND_0182, '$.amount'), ',', '.'))
| AMOUNT | T_TYPE | MSGTOSEND_0182 |
|---|---|---|
| 123456789,92 | CREDIT | {"amount":1.2345678992E8,"notes":"Nomina a Martin","transactionDetails":{"transactionChannelId":"_ach_enviada"}} |
select *
from zmt join po
on ZMT.AMOUNT = JSON_VALUE(PO.MSGTOSEND_0182, '$.amount' RETURNING NUMBER)
| AMOUNT | T_TYPE | MSGTOSEND_0182 |
|---|---|---|
| 123456789,92 | CREDIT | {"amount":1.2345678992E8,"notes":"Nomina a Martin","transactionDetails":{"transactionChannelId":"_ach_enviada"}} |