Re: [libreoffice-users] SQL statement in Base query
In article 500ea6e8.4030...@gmail.com, Jay Lozier wrote: 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. 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 -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] SQL statement in Base query
On 07/24/2012 01:39 PM, Mark Stanton wrote: In article 500ea6e8.4030...@gmail.com, Jay Lozier wrote: 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. 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 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. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] SQL statement in Base query
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
--
For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
