Bug 98857

Summary:	BASE: Create Query Design Wizard generates wrong ORACLE SQL queries
Product:	LibreOffice	Reporter:	Jan <test.benutzer.77>
Component:	Base	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED NOTABUG
Severity:	normal	CC:	aron.budea, lionel, serval2412
Priority:	medium
Version:	5.0.5.2 release
Hardware:	All
OS:	Windows (All)
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=90561 https://bugs.documentfoundation.org/show_bug.cgi?id=101732
Whiteboard:
Crash report or crash signature:		Regression By:

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.