Query result disappears when scrolling to bottom

Creating tables and queries

Query result disappears when scrolling to bottom

Postby teneighty » Mon Nov 23, 2015 2:23 am

This is strange to me, I wonder if this is a bug. I have a query that builds a result table from two tables:

Code: Select all   Expand viewCollapse view
SELECT "Suites"."Room", "Counts"."Room" AS "Room or Suite", "Counts"."Category", "Counts"."Item", "Counts"."Quantity"
FROM "Counts"
LEFT JOIN "Suites" ON "Counts"."Room" = "Suites"."Suite"


It is an HSQLDB embedded database file. When I scroll down, when I get to the bottom of the result table the whole thing disappears. The columns are still shows, and there is one empty row. I hit refresh or run query and it all comes back, but I cannot touch the very bottom of the result table or it disappears, every time.

I actually use this query as a subquery to sum "Quantity" for different "Item" values. "Category" makes it easy to filter results. Although it's not absolutely critical, it would be nice to be able to review the results of the above query as it serves as a master list.

I would use only one table, "Counts", if every room was unique, but sometimes many rooms are the same. Instead of counting their contents every time, I have a separate table "Suites" where I define suites or room templates and associate those with individual room numbers. LEFT JOIN I believe, in the above query expands, or duplicates, entries in the "Counts" table for any template rooms. Example: "Counts" contains two entries for room A. If "Suites"."Suite" has 6 entries for A, rooms 101 through 106 for example in "Suites"."Room", then the results will return 6 rows for each of the two rows for A in the "Counts" table, with the real room number in another column. If if an entry contains 107 in "Counts"."Room" and 107 does not occur in "Suites"."Suite" then nothing happens, it returns exactly what is in the "Counts" table.

See partial table examples below, to illustrate the contents and interaction of data in the two tables, if it is potentially relevant. Result shows 5 type C lights and 12 (2+2+2+2+2+2) type B lights in the query results.

Counts
| Room | Category | Item | Quantity |
| 107 | Lights | C | 5 |
| A | Lights | B | 2 |


Suites:
| Suite | Room |
--------------------
| A | 101 |
| A | 102 |
...
| A | 106 |

Results
| 101 | A |
| 102 | A |
...
| 106 | A |
| (null) | 107 |

This is the only query in this project that uses two sources and LEFT JOIN, so I wonder if the disappearing act has something to do with that. Have I done something wrong/undesirable/unwise in the SQL code? Queries doings SUMs based on results of this query (as a subquery), work fine.
OpenOffice 4.1.2 on Kubuntu 14.04.3
teneighty
 
Posts: 7
Joined: Sun Nov 22, 2015 6:11 am

Re: Query result disappears when scrolling to bottom

Postby UnklDonald418 » Tue Dec 08, 2015 6:53 pm

Since it has been so long since your post and there have been no replies I will throw in my 2 cents worth.
I built a couple of tables and tried your query. I get results similar to what you show, but no problems with disappearing results. I am running it on a Windows based computer.
Perhaps the problem is related to Kubuntu.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.7 & LibreOffice 6.1.5.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1286
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Query result disappears when scrolling to bottom

Postby teneighty » Wed Dec 09, 2015 4:35 pm

That is interesting. Thanks for testing that.
OpenOffice 4.1.2 on Kubuntu 14.04.3
teneighty
 
Posts: 7
Joined: Sun Nov 22, 2015 6:11 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest