topic Why does Databricks SQL drop ending 0 in decimal data type in Data Engineering

Why does Databricks SQL drop ending 0 in decimal data type

gbradley145 — Thu, 27 Oct 2022 14:26:55 GMT

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?

Re: Why does Databricks SQL drop ending 0 in decimal data type

UmaMahesh1 — Wed, 23 Nov 2022 13:19:44 GMT

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.

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

Re: Why does Databricks SQL drop ending 0 in decimal data type

Anonymous — Thu, 05 Jan 2023 06:15:27 GMT

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!