SQL statement in Base query

elderdanlewis · July 18, 2012, 6:24pm

The FROM clause is where I am having errors returned. The outline that I am using for this clause is:

FROM "Table name" [{CROSS | INNER | LEFT OUTER | RIGHT OUTER} JOIN "Table name" ON Expression] [, ...]

I have two tables with a child parent relationship:

"Persons"."P_ID" = "Orders"."P_ID"

These queries run without errors: (INNER JOIN has no fields with a NULL value; LEFT OUTER JOIN has NULL values in the OrderNo field; RIGHT OUTER JOIN has NULL values for the two fields in the "Persons" fields.)

SELECT "Persons"."Last", "Persons"."First", "Orders"."OrderNo"
FROM "Persons" INNER JOIN "Orders" ON "Persons"."P_ID" = "Orders"."P_ID"

SELECT "Persons"."Last", "Persons"."First", "Orders"."OrderNo"
FROM "Persons" LEFT OUTER JOIN "Orders" ON "Persons"."P_ID" = "Orders"."P_ID"

SELECT "Persons"."Last", "Persons"."First", "Orders"."OrderNo"
FROM "Persons" RIGHT OUTER JOIN "Orders" ON "Persons"."P_ID" = "Orders"."P_ID"

But the following SQL creates an error when the following is run:

SELECT "Persons"."Last", "Persons"."First", "Orders"."OrderNo"
FROM "Persons" CROSS JOIN "Orders" ON "Persons"."P_ID" = "Orders"."P_ID"

The error message: The data can not be loaded. Column not found: "P_ID" in statement ...

I know that this outline will create a CROSS JOIN for any pair of tables:

SELECT "table1 name"."field name", "table2 name"."field name", ... FROM "table1 name", "table2 name

So, it seems that this is one place where we should ignore the manual (HSQLDB user guide 1.8). It may be that this has been corrected in the 2.2.8 user guide. It uses

FROM "table1 name" CROSS JOIN "table2 name"

OR

FROM "table1 name", "table2 name"

--Dan

Villeroy · July 18, 2012, 6:56pm

On this simple level I would consider HSQLDB as free of bugs whereas the
primitive Base parser fails in many different ways.
Did you try the direct SQL mode (SQL view, menu:Edit>Run SQL directly)? If
you get reasonable result in direct mode, it is one of many bugs in the
Base's SQL parser.

elderdanlewis · July 18, 2012, 8:40pm

Andreas Säger wrote:

On this simple level I would consider HSQLDB as free of bugs whereas the
primitive Base parser fails in many different ways.
Did you try the direct SQL mode (SQL view, menu:Edit>Run SQL directly)? If
you get reasonable result in direct mode, it is one of many bugs in the
Base's SQL parser.

--
View this message in context: http://nabble.documentfoundation.org/SQL-statement-in-Base-query-tp3996273p3996282.html
Sent from the Users mailing list archive at Nabble.com.

That is the one thing that I forgot to mention: I get the same message when using direct SQL mode. I even tried using Tools > SQL, but with the same message.

--Dan

Ferry_Toth · July 23, 2012, 8:46pm

Did you try with another database as a backend? I like postgres as a
backend a lot.

Ferry

Dan schreef op wo 18-07-2012 om 16:40 [-0400]:

elderdanlewis · July 23, 2012, 9:55pm

Ferry Toth wrote:

Did you try with another database as a backend? I like postgres as a
backend a lot.

Ferry

I am writing a chapter of the Base Guide that includes using SQL in the Query Design dialog. Chapter 8 of the Base Guides covers using HSQLDB 2.2.8, MySQL, and PostgreSQL as back ends. So, I am not there quite yet. It seems that because Base uses HSQLDB 1.8, it does not conform with the user guide for the database engine it uses.
When I get to that point, I will need to research how each of these backends use SQL.
--Dan

Tom_Davies · July 24, 2012, 7:06am

Hi
If only it were possible to somehow communicate with people from the various back-end projects perhaps even ask them to write-up a brief sub-chapter on how to use their back-end in conjunction with Base. I wonder if any of them know anything about the use of Sql in their projects.

The BoD want to sit&wait rather than be pro-active about this sort of collaboration.

Regards from
Tom

planas · July 24, 2012, 1:45pm

Hi
If only it were possible to somehow communicate with people from the various back-end projects perhaps even ask them to write-up a brief sub-chapter on how to use their back-end in conjunction with Base. I wonder if any of them know anything about the use of Sql in their projects.

The BoD want to sit&wait rather than be pro-active about this sort of collaboration.

Regards from
Tom

Actually most users of different backend db will be familiar with the SQL version of that backend. What users need are two things: a connector between Base and the backend and instructions on connecting to that backend.

SQL is the standard query language for relational databases so if one writes queries for a relational database one should know a dialect of SQL. SQL has a published international standard that db developers follow. The problem is that each developer will add non-standard extensions for their db making the resulting code possibly not portable. An example is loading/exporting data from a file into a database is defined in the SQL specifications so each db has its extension to do the same thing. Each extension needs the same information but has a different syntax for the command.

Mark_Stanton · July 24, 2012, 5:39pm

The problem is, I think, that Base does some "pre-processing" to check the SQL, which
causes a problem, particularly in this context (non-standard SQL). Although there are
some helpful points to it are there enough?

Regards
Mark

planas · July 24, 2012, 6:07pm

Another question is whether a generic connector versus a specific connector (say for MySQL) will more problematic. I would hope a specific connector would address using some of the extensions which a generic connector probably would not. Also, I would expect Base to lag behind the current release of most backends. New features in the current release may not be supported by Base or the connector. The severity of this issue probably depends on whether the db project/vendor explicitly supports using Base as a front end or are they supplied by others (independent or AOO/LO).

I prefer to use Base for entry forms and predefined queries and use other db specific tools for administrative tasks and db design.

Villeroy · July 24, 2012, 7:30pm

Am 23.07.2012 23:54, Dan wrote:

Ferry Toth wrote:

Did you try with another database as a backend? I like postgres as a
backend a lot.

Ferry

I am writing a chapter of the Base Guide that includes using SQL
in the Query Design dialog. Chapter 8 of the Base Guides covers using
HSQLDB 2.2.8, MySQL, and PostgreSQL as back ends. So, I am not there
quite yet. It seems that because Base uses HSQLDB 1.8, it does not
conform with the user guide for the database engine it uses.
When I get to that point, I will need to research how each of
these backends use SQL.
--Dan

Hi,

SELECT <field list> FROM A CROSS JOIN B
... is a more explicit way to say...
The older and more common syntax is:
SELECT <field list> FROM A,B
... which creates the cartesian product, which includes all combinations of A and B with a row count of COUNT(A.*)*COUNT(B.*)
Both syntax variants can be limited by a WHERE clause.
In the 90ies such queries where not exactly equivalent to INNER JOINs. A cross join with WHERE clause used to be much slower because it created the full cartesian product first and then filtered out the unwanted rows. Only the [INNER] JOIN ... ON... syntax triggered certain optimizers. Today this is not an issue anymore. At least HSQLDB performs equally well with any syntax variant

SELECT <field list> FROM A CROSS JOIN B WHERE A.BID=B.ID
SELECT <field list> FROM A, B WHERE A.BID=B.ID

SELECT <field list> FROM A JOIN B ON A.BID=B.ID
SELECT <field list> FROM A INNER JOIN B ON A.BID=B.ID

HSQLDB supports all of these with equal results and performance. Base parses them correctly.

The ultimate SQL reference for all the common database engines: