How to define the type/precision of a calculated column in a query?

[mr_bum]

Dear all,

I am new to this forum as well as to Base and SQL, so please be indulgent if my question is trivial. I’d also like to add, that I’ve already searched for an answer to my problem, but without finding a real eye-opener.

In the enclosed example I have included a query with a division. My problem was that the result of this division was automatically converted to an integer value, e.g. 123/10 =12.

Obviously, it is not a matter of the column format, cause when I set this to show more decimals, the format does change, but the value remains the same, i.e. 123 / 10 = 12.00.

Just when I was about to post this question, it came across my mind that the output format could be determined by either the determined or the divisor. Hence I added two more columns to test this idea, with a result that is “correct” but nevertheless puzzling.

So my problem is basically solved, but I’d like to understand what’s going on:

• Is this behavior Base-related or is this related to SQL?

• Is Base/SQL supposed to work that way, or is this some kind of bug/glitch?

• If this is the normal behavior, what is the rule behind it? Does Base/SQL always choose integer fields?

• Is my approach correct in general, or should I have done anything differently, like setting up a result table or so?

Thanks a lot!

test.odb (4.2 KB)

[RobertG]

@mr_bum

result of this division was automatically converted to an integer value, e.g. 123/10 =12.

The field and the divisor are Integer. So the database will define the result as Integer. If you change the divisor as decimal with decimal places, the result will be shown in decimal places. In internal HSQLDB the result will be rounded. In internal Firebird the result won’t be rounded.

Column format has nothing to do with this. The result doesn’t show more decimal places as given by the divisor. If there are more than 2 decimal places the default format must be changed, because default is to show max. 2 decimal places. Switch the format in the last row of your query and you see: There is another decimal place.

The shown format is Base related, the result (no more decimal places than defined by divisor) is result of the database, so SQL.

So: If you will get a right result for a field, which for example should save currency, you have to divide by a decimal with 3 decimal places and round this.

[Villeroy]

SELECT "INT_A" * 1.000 / "INT_B" AS "Ratio" FROM "Somewhere"