Why does Databricks SQL drop ending 0 in decimal data type

gbradley145
New Contributor III

‎10-27-2022 07:26 AM

All,

I have a column, RateAdj that is defined as DECIMAL(15,5) and I can see that the value is 4.00000, but when this gets inserted into my table it shows as just 4.

%sql
 
SELECT LTRIM(RTRIM(IFNULL(FORMAT_NUMBER(RateADJ, '0.00000'), '0.00000')))

This is a small example of this. Other columns are set the same, when SELECTing them, they could show as 152.32140 but when this gets inserted it is just 152.3214.

I am needing the 0's to hang around after they are inserted.

What is causing the ending 0's to drop off when getting inserted into the table?

UmaMahesh1
Honored Contributor III

‎11-23-2022 05:19 AM

Hi @Greg Bradley​ ,

Did you try casting your value to decimal before writing it ?

Ideally casting the value as decimal should preserve it's precision if defined.

Attaching a screenshot for the same.

imageDo reply if any other help is needed...Cheers.

Uma Mahesh D
Preview file
45 KB

Anonymous
Not applicable

‎01-04-2023 10:15 PM

Hi @Greg Bradley​ 

Hope everything is going great.

Checking in. If @Uma Maheswara Rao Desula​  answer helped, would you let us know and mark the answer as best? If not, would you be happy to give us more information?

Thanks!

0 Kudos