compare 2 tables in LO Base ->results in SQL error code 1000

[uglybug]

LO Version: 6.1.5.2, Raspbian 10 (buster), Base HSQLDB Embedded
both LO Base Tables have the same structure and the comparison column is Plant ID ( a 12 position NUMERIC field type )

i am trying to determine if 2 LO Base Tables are the same by using the following simple SQL statement.

SELECT ‘Plant ID’ FROM ‘tb_Planting4’
UNION
SELECT ‘Plant ID’ FROM ‘tb_Planting3’
ORDER BY ‘Plant ID’;

however the result is the SQL error statement below:

SQL Status: HY000
Error code: 1000
syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE

Q1: is the " UNION " the cause of this error ?

[ tried " JOIN " with the same resulting error statement ]

Q2: do you have a better approach to determining if two tables are identical ?

[ trying to ensure my backup table ( tb_Planting4 ) has the same data as the working table ( tb_Planting3 )

[Ratslinger]

Hello,

UNION will work if you turn on Run SQL command directly:

Now while not the best, it will provide a supposition of equality:

Test1

Test2

[Ratslinger]

As a further note, other databases may have additional functionality. One is MySQL with:

CHECKSUM TABLE reports a checksum for the contents of a table. You can use this statement to verify that the contents are the same before and after a backup, rollback, or other operation that is intended to put the data back to a known state.

[uglybug]

hi Ratslinger,
at 1st, i was unable to duplicate your efforts and continued to get the original errors over and over again.
after hours of trying upper/lower cases. single/double quotes reformatting the SQL statement, finally it worked.

problem was that i was totally unaware of “Run SQL command directly”; many hours later i accidentally discovered it in View->Toolbars->SQL.
also, your interface is almost identical to mine but the Icon for “Run SQL command directly”
is just very slightly different and looks so much like my “Run Query” icon.

fact is, that i misinterpreted “Run SQL command directly” to mean “Run Query” because on my interface was not displaying all the SQL icons.

i am providing these details not to confirm my lack of knowledge of LO Base but because it may help someone who is as unfamiliar with LO.
i had a difficult time on the forum finding an example or description of “Run SQL command directly” because others know exactly where it is located and what it looks like

[uglybug]

following up on the table comparison topic. i had seen this example on another forum.
SELECT * From tableA
Minus
SELECT * From tableB

which i could not get to work in LO Base.
i used ‘Union’ instead of ‘Minus’ because it works in LO Base.
( thanks to @ Ratslinger )

is there a compatible command for ‘Minus’ in LO Base using HSQLDB embedded tables ?
[ again, the objective is to determine the difference between two tables ]

[uglybug]

Update: after reading documents for HSQLDB it was apparent that the SQL command ‘MINUS’ is a part of this database system. So using the instruction provided by Ratslinger and the verified ‘MINUS’ command i was able to finally determine that my two tables, tb_Seeding3 & tb_Seeding4 do in fact have the same data.

[uglybug]

thanks for all your help @ratslinger and patience during my learning process.

an alternate solution which i tried before without success until i used this text formatting within Tools->SQL, as shown below.
(i know, the text formatting should not have an effect but, for now its the only way i could get it to work.) weird !

i was not able to show the exact formatting i used because i do not know how to get the TAB-character to display in this forum. all the SQL commands were left justified and the arguments were indented with keyboard-TABs.

[ equivalent to using the “Tasks” option ‘sql Create Query in SQL View…’ in the “Queries” panel, followed by
selecting the disk-drive Icon for “Run SQL command directly” which on my system has a " >_" image at the bottom of the Icon ].

SELECT

"Plant ID"

FROM

"tb_Seeding3"

MINUS

SELECT

"Plant ID"

FROM

"tb_Seeding2";