CODEWITHSUNDEEP
sqloracle-databasecastingdecimalcurrency
Lyndey
Lyndey

Reputation: 77

Currency column with no decimals: add in decimal

I've been reviewing a currency column that has no decimal spaces. It's an output from a legacy system loaded into our Oracle database.

If the field has three or more numerals it should have a decimal at three spaces right.

If the value has less than three numerals, it should have a decimal and a leading zero.

For example:

I've tried using cast, but I received the error 'invalid datatype.'

It's a basic select statement:

    select 
    customer_id
    cast(ENDING_BALANCE as (decimal(10,3)) as Bal_1
    from Current_Balances

Any suggestions would be appreciated.

Upvotes: 0

Views: 78

Answers (1)

Caius Jard
Caius Jard

Reputation: 74740

I think you need to cast it to a number and divide by 1000

SELECT CAST(CAST('2050' as INT)/1000 as DECIMAL(10,3)) FROM DUAL

If you really mean to have the output format looking like that, you need to TO_CHAR it

SELECT LTRIM(TO_CHAR(CAST('2050' as INT)/1000, 'FM0.000'), '0') FROM DUAL

Upvotes: 2

Related Questions