Bug 98857 - BASE: Create Query Design Wizard generates wrong ORACLE SQL queries
Summary: BASE: Create Query Design Wizard generates wrong ORACLE SQL queries
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.0.5.2 release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2016-03-24 09:25 UTC by Jan
Modified: 2016-08-26 02:37 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Jan 2016-03-24 09:25:41 UTC 
Connected BASE to Oracle XE database via ODBC. Tables are in Oracle.

Used "Create Query Design Wizard.." to generate a query against Oracle.

It generated the following SQL Syntax which is rejected by Oracle wit ORA-00933: SQL command not properly ended.

SELECT "CUST"."ID", "CUST_RATE"."CUST_ID" 
FROM "KIS_TEXI"."CUST_RATE" AS "CUST_RATE", 
"KIS_TEXI"."CUST" AS "CUST" 
WHERE "CUST_RATE"."CUST_ID" = "CUST"."ID"

Correct (for Oracle) would be:
SELECT "CUST"."ID", "CUST_RATE"."CUST_ID" 
FROM "KIS_TEXI"."CUST_RATE" "CUST_RATE", 
"KIS_TEXI"."CUST" "CUST" 
WHERE "CUST_RATE"."CUST_ID" = "CUST"."ID"

The use of the "AS" keyword in the FROM clause for table aliasing is not allowed in Oracle SQL (whilst it is in SQLServer, MySql, Postgres).

Work around: manually correct each generated SQL and remove the "AS" keyword from the FROM clause
Comment 1 Julien Nabet 2016-03-24 22:29:08 UTC 
I'll try to find some time to take a look.
Comment 2 Lionel Elie Mamane 2016-03-25 06:06:45 UTC 
That's like bug 81213 but with AppendTableAliasInSelect
Comment 3 Lionel Elie Mamane 2016-03-25 06:08:42 UTC 
Jan, I think there is an "advanced property" that controls whether "AS" is put in front of table aliases. Try to toggle it and see if after making a change in query design "AS" is removed.
Comment 4 Lionel Elie Mamane 2016-03-25 06:12:36 UTC 
(In reply to Lionel Elie Mamane from comment #2)
> That's like bug 81213 but with AppendTableAliasInSelect

Sorry, was too fast. Maybe another similar property.
Comment 5 Julien Nabet 2016-03-28 11:55:31 UTC 
Sorry can test this since I don't have Oracle (dumb me!)
Jan: following Lionel's comment, any update?
Comment 6 Aron Budea 2016-05-12 15:53:42 UTC 
Jan, please try the setting suggested by Lionel, specifically this in the related bug report:
https://bugs.documentfoundation.org/show_bug.cgi?id=81213#c4

If your issue still persists, change the status of the bug report back to UNCONFIRMED.