Oracle does not accept the "AS" in select statements like this: select WHATEVER from "TABNAME" AS "ALIASNAME" This is, what the reportbuilder generates. If I ommit the "AS", the statement is executed correctly: select WHATEVER from "TABNAME" "ALIASNAME" I am using Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product But Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 does not accept the generated syntax too.
Lionel: searching "AS" in reportbuilder module gives 2 relevant locations: http://opengrok.libreoffice.org/xref/core/reportbuilder/java/org/libreoffice/report/SDBCReportDataFactory.java#559 + line 565 of this same file. Should we just remove this "AS" since it's optional for most (all?) RDBMS and illegal at least for Oracle? (I just found this ref http://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle) BTW: I can make the change obviously but not test it.
(In reply to comment #1) > Lionel: searching "AS" in reportbuilder module gives 2 relevant locations: > http://opengrok.libreoffice.org/xref/core/reportbuilder/java/org/libreoffice/ > report/SDBCReportDataFactory.java#559 + line 565 of this same file. > > Should we just remove this "AS" since it's optional for most (all?) RDBMS > and illegal at least for Oracle? Yes, please do that. However, I think it will not be enough, since this is a backup code branch that I've only ever seen taken with gcj (never with Sun/Oracble Java). > BTW: I can make the change obviously but not test it. Activate the database's query logging, you'll see the queries sent to the database. I don't think this is per se related to the report builder (except the locations you linked to), the report builder does not really create queries. It gets a query as a parameter (the data source), and modifies it a bit (to add/remove ORDER BY clauses for sorting, GROUP BY clauses for groupings, etc, etc). But AFAIK it does that through a query composer UNO service. So the soure of the stuff is most probably from our general query designer / composer. That can be tested by creating a query with the query designer and switching to SQL view. Should be in dbaccess/source/ui/querydesign/ and/or dbaccess/source/core/api/SingleSelectQueryComposer.cxx and/or in dbaccess/source/core/api/querycomposer.cxx. I'd be most happy if you took care of this.
Johann, could you please attach an example .odb file? I want to see the query, and "from where" it is constructed, to check that my conjecture in my previous comment is correct. Thanks in advance.
Johann, actually looking a bit at the code, we have a setting for that. Go to Edit / Database / Advanced Settings. In "Special Setting", *uncheck* (unset) the "Use keyword AS before table alias names". Please let us know if it solves the issue *after* you refresh the query used in the report: just make any change to the query, save it, make the opposite change, save it again. And then rerun the report. Julien: still remove the "AS" in the two places you found. Please also set this setting to *FALSE* by default, in the "general" drivers (those not linked to a specific RDBMS: odbc, jdbc, etc, *not* MySQL, PostgreSQL, ...). It corresponds to "UseKeywordAsBeforeAlias" in files connectivity/registry/*/org/openoffice/Office/DataAccess/Drivers.xcu
(In reply to comment #4) > Julien: still remove the "AS" in the two places you found. Please also set > this setting to *FALSE* by default, in the "general" drivers (those not > linked to a specific RDBMS: odbc, jdbc, etc, *not* MySQL, PostgreSQL, ...). > It corresponds to "UseKeywordAsBeforeAlias" in files > connectivity/registry/*/org/openoffice/Office/DataAccess/Drivers.xcu Also "GenerateASBeforeCorrelationName" in dbaccess/source/core/dataaccess/ModelImpl.cxx, function ODatabaseModelImpl::getDefaultDataSourceSettings, change that to false. And connectivity/source/drivers/odbc/ODriver.cxx, function ODBCDriver::getPropertyInfo, again "GenerateASBeforeCorrelationName" and same in connectivity/source/drivers/jdbc/JDriver.cxx and connectivity/source/commontools/dbmetadata.cxx function, line "bool doGenerate( true );" All this hoping that no other widely used RDBMS *requires* this 'AS'...
Thank you Lionel for all these information, I'll give it a try.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=568778874429595855b435792e5ebecd52956dae Resolves fdo#81213: Wrong table-clause generated by reportbuilder The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
For 4.3: https://gerrit.libreoffice.org/10469/ for 4.2: https://gerrit.libreoffice.org/10470 I let you check these Lionel before putting this tracker to FIXED.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-4-3": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0d5d8c22f7be41d408d8ee4012ef1a6f4368423e&h=libreoffice-4-3 Resolves fdo#81213: Wrong table-clause generated by reportbuilder It will be available in LibreOffice 4.3.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
For the record, 4.2 one has been abandonned (see https://gerrit.libreoffice.org/#/c/10470/) Let's put this one to FIXED now (so from future release 4.3.1)
*** Bug 52156 has been marked as a duplicate of this bug. ***