Base SQL Union Syntax

[philhaney]

I originally asked a question about Multiple Sub Reports. I was very politely directed to this question Libre Data - multi level report, which answered my question perfectly, by providing an explanation and this sample database: InvoiceForumPost.odb.

The sample database contains a SQL UNION command, and I have a few questions about the syntax.

Here is the result:

And here is the, annotated, command:

-I looked up the UNION command online, and realized that I don’t need the AS keyword in the second SELECT statement.

-I was able to intuit from the example, that I can create a new entry in the recordset which makes the whole thing work (A).

-I’m wondering why the query works with the AS keyword missing in the "Materials"."ServiceDate" "Service Date" portion of the first SELECT statement (B).

-I’m also wondering why the name of the table in the FROM clause is repeated in each SELECT statement (C), instead of only being entered once.

Thanks for your help.

[Ratslinger]

Hello,

As indicates an alias. It can be eliminated without any change. It is mainly used for clarity. A Union statement does require all fields to be the same. Alias here is necessary - with or without the As. Do not remember why I did each one differently. As you can see it creates no problem.

In being consistent, this is another alias. It could have been eliminated. This may also have arisen from some LO problems in the past where data was not displaying correctly if some items had an alias and some did not. In this case it works in the current LO version without the alias.

For SQL, it is always best to refer to the database documentation. Here is the one for HSQLDB v1.8 (included with Base) → Hsqldb User Guide

[mgl]

Hello

Field aliases are mainly used to improve the legibility of the column titles. By example, “ServiceDate” looks odd when “Service Date” looks “normal”.

Table aliases are mainly used to reduce to length of the clauses. Some say it improves the legibility of the clauses, some say it does not.

By example, if you write FROM “Materials” “M”…
then you are able to write SELECT “M”.“ServiceDate” “Service Date”

Kind regards, Michel

[philhaney]

Ratslinger, thank you for your answer. You’ve helped so many people. I was searching for something the other day, and I was surprised to come across a question that you asked… Mgl, thank you for your comment, too.