cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Why does Databricks SQL drop ending 0 in decimal data type

gbradley145
New Contributor III

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?

3 REPLIES 3

UmaMahesh1
Honored Contributor III

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.

Anonymous
Not applicable

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!

silvathomas
New Contributor II

The value goes to 10,000 values and having the things done to run a fast execution, and I am also Sociology Dissertation Help with the reduction of pages.

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!