Have written this query.
SELECT lastname, firstname, zip, families.familyid
FROM families
Inner join participants
on families.familyid=participants.familyid
ORDER BY lastname;
and this works as expected, but if I try to use AS to generate an alias for zip to postcode I get (missing operator) syntax error I am new to SQL so am obviously doing something wrong.
Can you show the exact query and the exact error message?
Have tested w HSQL v1.8 (default embedded) and see no problem. Also please give OS, LO version and whether you are using default Base, split or external (which one).
Unfortunately, the bug still exists in LO 6.1.1.2 running on Windows 10 64 bit. ‘AS’ statement doesn’t work with database in MS Access 2003 format. Different quoting symbols (` ’ " [) doesn’t help…
@vakulenko Have no problems with alias in Access connection. However, I am on Linux and connect method is JDBC using UCanAccess connector. This connector also works in Windows. See my answer on this post for instructions → mdb files not loading to base.
Note: Recent test used back tick to surround field & table names.
Suggest debugging in Menu Tools SQL, if you are not already doing this.
Suggest quoting identifiers. While this is not always necessary, it serves to clarify distinctions from reserved words at times. ("", `` or [] in Embedded HSQLDB 1.8). (Back tick quote in MariaDB or MySQL).
If it doesn’t work, break it down until it does, e.g. try,
SELECT zip As xx
FROM families INNER JOIN participants ON families.familyid=participants.familyid;
or even simplify to
SELECT zip As xx FROM families;
Until it works, then build it back together one step at a time, testing as you go. Use a text editor in another window to keep your code so you can cut and paste to go backwards and forwards in your construction.
Here is a working HSQLDB example for you:
In this database: Lookup3.odb
SELECT "Name" As "N", "Product" As "P"
FROM "Companies" INNER JOIN "Products"
ON "Companies"."Company ID"="Products"."Company ID"
ORDER BY "Name";
This also works in this case:
SELECT Name As N, Product As P
FROM Companies INNER JOIN Products ON Companies.[Company ID]=Products.[Company ID]
ORDER BY Name;
Returns status:
Command successfully executed.
And outputs:
Big Dog Products,Bark collar,Big Dog Products,
IBM,pc,IBM,
IBM,IBM 370,IBM,
Intel,8086,Intel,
Intel,8080,Intel,
Main Street Tools,screw driver,Main Street Tools,
Main Street Tools,wrench,Main Street Tools,
Rayco,diode, vaccume tube,Rayco,
Toyota,takoma,Toyota,
Toyota,echo,Toyota,thanks for the answer. Have broken it down to
SELECT “ParticipantStatus” AS “Member Status” from “Participants”;
But still not working.
I think the problem is in the fact that I am connected to an Existing access database file, (.accdb)
though it works ok in open office using design view but not if I copy and past the SQL query. However design view does not work in Libreoffice.
I copy and pasted the SQL query generated from the working design view in open office to Libreoffice but it will not work. Must be some gremlin in the works somewhere.
Cheers
Terencezl
OS is windows 10. LO 5.3.1.2
Are you with the right access version?, there are two ‘access’ and ‘access 2007’
Have tried both
@Terencezl Please realize when asking a question it is important you provide critical information such as the type of data base you are dealing with. And although you still haven’t mentioned numerous other things (LO version, OS, connection type to Access all helpful items in dealing with this) your possible problem is how you are defining the SQL & what option is on.
The Run SQL command directly switch on the toolbar controls whether the SQL runs through the Base interpreter or gets passed through directly. If surrounding your field/table names with double quotes " you will get an error with the switch ON. It should work with the switch OFF. To use with switch ON, you need to use a back tick to surround names as expected by Access:
SELECT `ParticipantStatus` AS `Member Status` from `Participants`;
This should work whether the switch is on or off.
If this does not solve your problem, you need to provide the additional information as mentioned above.
@Terencezl I have a Base database front end connecting to Access Tables. I have not used aliases before so I did a test. I have found the same problem - not able to create alias for a field. Tried various punctuation versions. Tried direct SQL. In fact when connected to assess Tables no punctuation is required
SELECT FieldName FROM TableName
works but
SELECT FieldName AS AliasName FROM TableName
fails with this error -
SQL Status: 3075
Error code: -2147217900
Syntax error (missing operator) in query expression ’FieldName AliasName'.
It appears to be ignoring the AS. Tried on LO ver. 4.4.1.2 and ver. 5.0.2.2.
If I copy the Tables to a Base embedded database I can create aliases without any problems. It looks like a problem with the driver for Access in LO.
I have not had any other problems doing complex SELECT statements and INSERT and UPDATE in SQL.
Did you say it works in OpenOffice?
@peterwt You are probably correct in that it is the connector. I am using UCanAccess connector (JDBC) on various Linux systems without a problem. This is with LO v5.1.x, 5.2.x & 5.3.1.2 and none have this problem. In the case of @Terencezl, punctuation is needed because of the space in one of the field names, but I had no problem with that either.
@peterwt and @Ratslinger I have worked around the problem by exporting the tables I want to calc then opening a new database and importing the tables. Everything works fine both in design view and SQL. Thanks for all the advice guys I appreciate your time.
WIndows 10 - file .mdb - odbc connection: alias (AS) not recognized.
Resolved putting pure sql commands in sql query of search (disabled graphic interface and tick the check “run directly the command”).