Issue 110535 - Report fails with error 'SQL Status: S1000' on grouping with MSAccess
Summary: Report fails with error 'SQL Status: S1000' on grouping with MSAccess
Status: CLOSED FIXED
Alias: None
Product: Base
Classification: Application
Component: ReportBuilder (show other issues)
Version: OOO320m12
Hardware: PC Windows XP
: P3 Trivial (vote)
Target Milestone: OOo 3.3
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-03-31 20:23 UTC by eremmel
Modified: 2010-08-02 13:19 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments
Reproduction Base document (79.26 KB, application/vnd.sun.xml.base)
2010-03-31 20:30 UTC, eremmel
no flags Details
MsAccess database corresponding with issue110535.odb (160.00 KB, application/octet-stream)
2010-03-31 20:31 UTC, eremmel
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description eremmel 2010-03-31 20:23:22 UTC
When a report is made based on a query accessing MSAccess and this report is
using grouping the following error is generated: 
Error:   
  SQL Status: S1000
  An error occurred while creating the report.
Information:
  An exception of type com.sun.star.lang.WrappedTargetException was caught.
Error:
  The specified field '`X`' could refer to more than one table listed in the
FROM clause of your SQL statement.
Error:
The specified field '`X`' could refer to more than one table listed in the FROM
clause of your SQL statement.

This error occurs when there is more than one table in the from-clause of query
involved and the field X from the select clause of that query is available in
more than one table.

Possible workarounds are:
1 Edit the report and change the property 'Analyze SQL command': No at the
Data-tab of the root element of the report (Navigator). But than no parameters
can be used.
2 Make from the query derived table and query that. (see Query2 in issueN.odb)

It looks like that when 'Analyze SQL command' is active the Meta-data MSAccess
interface is used, but the implementation or the interface itself has some short
comings.

Please find attached a reproduction document issueN.odb and a MSAccess database
issueN.mdb with N is this case number.

In the reproduction document one can find:
Queries:
Query1: query that joins two tables with same field names ID and Value.
Query2: query that uses Query1 and workaround 2.

Reports:
q1.ReportNoGroup: report bases on query1 without grouping and with 'Analyze SQL
command' is active, so runs fine.
q1.ReportWithGroupingFails: report bases on query1 with grouping on ID and with
'Analyze SQL command' is active, this is failing.
q1.ReportWithGroupingOK: report bases on query1 with grouping on ID and with
'Analyze SQL command' is NOT active, this is OK.
q2.ReportWithGroupingOK: report bases on query2 with grouping on ID and with
'Analyze SQL command' is active, this is OK (workaround 2).
Comment 1 eremmel 2010-03-31 20:24:33 UTC
This is issue is also identified with Dev300m75
Comment 2 eremmel 2010-03-31 20:30:01 UTC
Created attachment 68680 [details]
Reproduction Base document
Comment 3 eremmel 2010-03-31 20:31:49 UTC
Created attachment 68681 [details]
MsAccess database corresponding with issue110535.odb
Comment 4 marc.neumann 2010-04-01 10:23:59 UTC
I can reproduce the problem in OOO320 m14 and DEV300 m71.


confirm , set target and send to the right developer
Comment 5 r4zoli 2010-04-01 10:55:14 UTC
On Windows 7(HU), with OOo 3.2, with java 1.6_18., I get error:"SQL Status: S1000
An error occurred while creating the report.",
 when run "q1.ReportWithGroupingFails" report.

More gives: A megadott mező ('`Value`') az SQL utasítás FROM záradékában
megadott egynél több táblára is hivatkozhat.

Rough translation: The given field ('`Value`') in FROM clause of SQL command can
reference more than one table.

All others running without problems.
Comment 6 ocke.janssen 2010-07-08 15:14:36 UTC
Fixed in cws dba33g.
Comment 7 ocke.janssen 2010-07-14 11:47:00 UTC
Please verify. Thanks.
Comment 8 marc.neumann 2010-07-20 08:53:02 UTC
verified in CWS dba33g

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=DEV300%2Fdba33g
Comment 9 r4zoli 2010-08-02 13:19:04 UTC
Verified in OOO320_m2
Closed.