SQL query not working when LibreBase connected to ORACLE

[ns309]

Hi,

I use the following select statement for a list box:
SELECT "Code" || ' - " ' || "BrandName" || '"' AS "Display", "Code" FROM "LU_BRAND" ORDER BY "Code" ASC

This works when the database is opened in an Embedded HSQLDB. However, when connected with Oracle JDBC, it gives me an error message saying the data content cannot be loaded. (I have created all the necessary tables in Oracle.)

In addition, the select statement changes to:
SELECT "Code" || ' - " ' || "BrandName" || '"' AS "Display", "Code" FROM "LU_BRAND" AS "LU_BRAND" ORDER BY "Code" ASC

i.e. there is an extra "AS" component in the statement, which I did not put in. I have tried deleting the extra text, saving the SQL, and re-running, but somehow the extra text appears again.

Please could anyone shed any light on this, and how to solve it?

Many thanks

[rudolfo]

Oracle doesn't like the AS keyword in table aliases. It is okay with column aliases though.
If you go to Edit->Database->Advanced Settings you can configure this for each database connection.
The deactivation of table aliases (as seen in the screenshot) was sometimes ignored (I think the queries that I did create with the designer did always use table aliases). But unchecking the "use AS keyword" helps. This is an dependent option, it can only be changed when the above setting "Append the table alias.." is active.

Advanced settings of database connections