Ask Your Question
1

AS statement to generate alias does not work

asked 2017-03-07 23:49:48 +0100

Terencezl gravatar image

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.

edit retag flag offensive close merge delete

Comments

Can you show the exact query and the exact error message?

robleyd gravatar imagerobleyd ( 2017-03-08 00:19:13 +0100 )edit
1

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).

Ratslinger gravatar imageRatslinger ( 2017-03-08 01:28:07 +0100 )edit

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 gravatar imagevakulenko ( 2019-02-05 11:40:27 +0100 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-02-11 06:00:21 +0100 )edit

4 Answers

Sort by » oldest newest most voted
0

answered 2017-03-08 05:13:53 +0100

EasyTrieve gravatar image

updated 2017-03-08 06:07:19 +0100

1) Suggest debugging in Menu Tools SQL, if you are not already doing this.

2) 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).

3) 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,
edit flag offensive delete link more
0

answered 2017-03-18 12:53:40 +0100

Terencezl gravatar image

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

edit flag offensive delete link more

Comments

OS is windows 10. LO 5.3.1.2

Terencezl gravatar imageTerencezl ( 2017-03-18 21:28:08 +0100 )edit

Are you with the right access version?, there are two 'access' and 'access 2007'

m.a.riosv gravatar imagem.a.riosv ( 2017-03-19 11:50:20 +0100 )edit

Have tried both

Terencezl gravatar imageTerencezl ( 2017-03-19 23:27:59 +0100 )edit
0

answered 2017-03-18 14:19:17 +0100

Ratslinger gravatar image

updated 2017-03-18 14:22:17 +0100

@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.

edit flag offensive delete link more
0

answered 2017-03-19 11:31:32 +0100

peterwt gravatar image

updated 2017-03-19 11:40:41 +0100

@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?

edit flag offensive delete link more

Comments

@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.

Ratslinger gravatar imageRatslinger ( 2017-03-19 13:21:52 +0100 )edit

@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.

Terencezl gravatar imageTerencezl ( 2017-03-19 23:37:37 +0100 )edit
Login/Signup to Answer

Question Tools

2 followers

Stats

Asked: 2017-03-07 23:49:48 +0100

Seen: 541 times

Last updated: Feb 05