0

I have 29 separate tables of council address data with identical field headers and thousands of entries. I am trying to find an address based on property id that should appear in at least one of the tables. I have been trying to use a union query;

SELECT "ST_NO_FROM","STREET","ST_TYPE" 
FROM "list_address_points_break_o_day" 
WHERE "PID" = 6413877 
UNION 
SELECT "ST_NO_FROM","STREET","ST_TYPE" 
FROM "list_address_points_brighton" 
WHERE "PID" = 6413877

Base is crashing every time I try to use this query and requires recovery. Does anyone have any idea why this is happening?

fredt
  • 24,044
  • 3
  • 40
  • 61
Axel Clark
  • 1
  • 1
    Try using **UNION ALL** (instead of just "union") it does not reduce the result to unique rows and so it consumes fewer resources. – Paul Maxwell Jan 14 '16 at 04:54
  • What's the error message? – StanislavL Jan 14 '16 at 05:27
  • What engine are you using? The default is embedded HSQLDB 1.8 which is not a very robust setup. – Jim K Jan 14 '16 at 06:19
  • Hey guys. I am not even getting an error message it is just dropping out entirely. I tried UNION ALL too and it was still doing the same thing. I am using HSQLDB 1.8 however besides interrogating a single line from the 29 tables that I have my needs are not too complex I believe. I am now looking at trying to build a CASE statement that will allow me to go directly to the individual table needed which is probably a mor e elegant solution anyway. Anybody who has a good example of that would be welcome to throw it up here! – Axel Clark Jan 14 '16 at 10:11
  • You do have the option Edit→Run SQL Directly selected, yes? The Base parser can't handle UNION so you have to make sure to bypass the parser and send the SQL directly to the backend (HSQLDB in your case). – Lyrl Jan 14 '16 at 16:04

1 Answers1

1

Be sure to use a split database setup and avoid embedded databases. From the link:

When the file is "closed," the latest contents are repackaged into a single zip-archive by *Office. This re-packaging process is problematic and commonly leads to file-corruption... So it's best to avoid these 'embedded database' files whenever possible. This means avoiding the wizard dubbed 'Create a new database' in Base.

HSQLDB (either 1.8 or a newer 2.x version) should be stable enough to handle the data. If the needs are very complex then a full-scale RDBMS such as MySQL could be used instead.

Jim K
  • 12,824
  • 2
  • 22
  • 51