BUG: Base not correctly filling in percentages for query

[Haunt]

So I made a base table that had multiple fields, but two of them are important to this issue. Field one expresses how many items are currently stockpiled. Field two expresses how many items there is room at maximum to be stored up.

So I made the query. Added fields: Item Name, Have, Total. Then in the next empty column I typed in “Have” / “Total” and in the alias labeled it as “%”. Then I clicked on Run Query. And then formatted the new column and chose Percent.

However, every single entry such as 67/78 is showing 0% full and there seems to be no way of correcting it.

So that is what it is currently doing. Now what it should be doing for example say I had 80 jars and all 80 of them were full the percent should say 100% while 32 jars with 31 being full should give 96% or 97% full depending on rounding.

So someone please explain to me how I can fix this to doing what I wanted. It should not be this hard to just display a percentage…

I can’t figure out why it is broken like this or how to fix it. Any answers?

[Ratslinger]

Hello,

While it seems to include a lot of information, need essential information. What specific LO version? What database is being used? What is the SQL statement used? What are the “Have” & “Total” field types?

It is possible you simply need to cast the field(s) with the problem.

Another possibility is that both “Have” & “Total” field are integers. In that case multiply one by say 1.0000 in your calculation.

[Haunt]

I originally created the thing using an old copy of a 5.2 LO. After ages trying to get it to work on my own I decided to seek help on the web and only then did I realize that it was quite a bit out of date. So hoping it was simply a problem of being too old to work right I checked for updates and saw that it was out of date by a lot so I DLed and started the database over from scratch in the new 6.3.1.2 (x64) version hoping it would solve the problem. It did not.

As for the SQL statement being used:
SELECT “Title”, “Have”, “Total”, “Have” / “Total” AS “Percent” FROM “Shop”

Simple and should not be giving this bugged results, and certainly not across two completely different versions of LO. I checked and both fields are set as Category type Numbers. I am willing to try anything to get this to work. You have no idea how many hours I have been putting into working on this problem trying to make something ridiculously simple actually work only to see it fail constantly.

[Ratslinger]

Hello,

I can only present what it appears to be the problem - a guess since the information asked for was not given.

HSQLDB embedded database used. Fields “Have” & “Total” originally defined as type Integer. Dividing these will not give desired results. If instead, as mentioned in my comment, you multiply one of these by a number with decimals, you can get the results wanted:

Table data used -

Fields are of type Integer so SQL contains additional multiplication to provide decimal places:

Formatting the column to percent as you stated:

If this is your situation, it is certainly not a bug. It is a matter of the data types used.

If this is not your situation, please provide the information originally asked for.

[Haunt]

Quote “If this is not your situation, please provide the information originally asked for.”

… not sure if we are just both confused or what. I provided all of the information you asked for in my response to you earlier.

Fields are not an Integer. The LO used was originally 5.2 then remade in 6.3.1.2.SQL statement: SELECT “Title”, “Have”, “Total”, “Have” / “Total” AS “Percent” FROM “Shop”.

Furthermore, I am sure it is a bug since I had to reboot that pc last night for a completely different issue and it was the first time downtime for that server in months. Prior to reboot the thing was still listing 0%. After the reboot it correctly was giving 96%, 78%, etc. No changes made by myself before / after reboot. Just the reboot itself fixed it.

Good news: Now working.
Bad news: Only fixed itself due to a windows restart, not from doing any manual changes.

[Ratslinger]

@Haunt,

Good to hear it is working. You did not provide DB being used. Also it would have been a help to know # of decimals set in “Number” type used.

Have not seen anyone else reporting this issue.

Edit: Also, just verified, Numeric type with no decimal positions will also cause erroneous results.

[Haunt]

Numeric type with no decimals was exactly what I had in my case.

As for the DB being used, my apologies I thought I had answered that when I said I was using base. I have been using whatever is the default database set when you download the suite, I was not even aware until you said anything that it could be changed.

Honestly I am not sure what this type of db is called or how I would find out what it is since it does not say anything special when I go to the About LibreOffice option. If it helps, the file is named Shop.odb and it is saved as type “ODF Database”. If that doesn’t answer question, when I try to right click the table window and side swipe the Database choice menu at bottom it is a blank list. “No selection possible” Edit Database options are also all three greyed out.

[Ratslinger]

@Haunt,

There are two different databases which ship with LO - HSQLDB embedded and Firebird embedded. One of these may be selected upon creating a Base file. You can see what you have on the bottom line of the opening .odb screen. You can also connect to many other databases - PostgreSQL, MySQL, SqLite, etc.

This is important to note which you are using when asking a question as each may include various unique functions not part of a standard.

Not important at this point since you have solved your problem but thought you would want this information if asking questions in the future.