CAST AS DECIMAL returns text, why?

Question

In a Libre Base query can anyone say why the following would fail to produce a numeric character with 4 decimals and instead return a column formatted as text showing two decimals? I want the query to store 4 decimals. Whether the internal CAST functions are included or not the column is always formatted as text. The data types of fs and ms_mass are both numeric with one decimal. Thanks.

CAST((CAST("fs_mass" AS DECIMAL (10,4)) + CAST( "ms_mass" AS DECIMAL (10,4)))*.001 AS DECIMAL (10,4))

What database is this — perhaps MySQL, PostgreSQL, HSQLDB 1.8, or Firebird Embedded? LibreOffice Base isn't a database; rather, it connects to one, possibly (for the last two options I listed) embedded into the .odb file. Adding a tag for the database will get people to see the question that know how to write queries for it. — Jim K
– Jim K, Commented Oct 29, 2024 at 15:36
Thanks, I created the database in Libre Base using the internal HSQL engine. I updated the tags as per your recommendation. — devitti
– devitti, Commented Oct 29, 2024 at 17:03
With a numeric (decimals=1) value of 1.2, entering select CAST((CAST("fs_mass" AS DECIMAL (10,4)) + CAST( "fs_mass" AS DECIMAL (10,4)))*.001 AS DECIMAL (10,4))from "Table1"; produced "0.0024", either by going to Tools > SQL or creating as Query1 and running directly. — Jim K
– Jim K, Commented Oct 30, 2024 at 14:20
Your question is pretty good, but I can't upvote this one because I had to spend awhile guessing. I think I eventually figured out what you're asking (see my answer), but in the future, it would be good to include a minimal, reproducible example. — Jim K
– Jim K, Commented Oct 30, 2024 at 14:38

Jim K · Accepted Answer · 2024-10-30 14:28:04Z

0

It sounds like the problem is how the output is displayed. For example, with:

select 1.2345 + CAST((CAST(1.2 AS DECIMAL (10,4)) + CAST( 1.2 AS DECIMAL (10,4)))*.111 AS DECIMAL (10,4)) from "Table1";

the query result screen showed "1.5" at first. So I right-clicked on the column header, just above the first result, and went to Column Format. Change to a number with 4 decimal places.

Result: 1.5009

Running with Tools > SQL does not require this formatting.

edited Oct 30, 2024 at 14:28

answered Oct 30, 2024 at 14:18

Jim K

14.1k2 gold badges26 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

devitti Over a year ago

Jim, This took some thought but you are right that the column format in the output in Base is not the same as when the output is copied into Libre Calc. Then the output appears correct with four digits. Thanks for the help.

Collectives™ on Stack Overflow

CAST AS DECIMAL returns text, why?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related