Apache OpenOffice (AOO) Bugzilla – Issue 13318
[RFE] don't generate table aliases if not necessary
Last modified: 2006-05-31 14:29:06 UTC
Hi, I'm not sure if this answered somewhere - have searched but not found an issue or faq that corresponds The problem relates to OOo aliasing tables in a query created in design mode, to the same name as the original table. In our environment this is rejected by the UniVerse database manager which is expecting an alias other than the original table name. EG. SELECT "AT_ID", "OFFICE" FROM "CUSTOMER_MASTER" "CUSTOMER_MASTER" fails whereas SELECT "AT_ID", "OFFICE" FROM "CUSTOMER_MASTER" "CM" succesfully returns the desired result as does SELECT "AT_ID", "OFFICE" FROM "CUSTOMER_MASTER" A solution might be to generate a unique alias similar to the way tools like Cognos Impromptu and others eg SELECT "AT_ID", "OFFICE" FROM "CUSTOMER_MASTER" "T1.CUSTOMER_MASTER" or to allow the user to use own aliases or not use aliases. Is there some way to control the use of aliases. We got around this issue in OOo 1.0 as it was possible to create the query in design mode and then turn off design mode so that the SQL could be edited and "corrected" i.e either remove the aliases or edit into the query "real" aliases. However, from OpenOffice 1.0.2 one cannot turn off design mode if a problem is detected in the query. Table joins are also affected and it appears that one cannot select join types other than inner join in OOo1.0.2 and OOo1.1 beta Many thanks for a great product Ian Stuart
Hi Ian, it seems that Spreadsheet is the wrong component. I've set it to database access. I think this component fits better. Frank
Ian, thanks for this report. Though I understand the general problem (alias name = table name), there are some things unclear to me: > However, from OpenOffice 1.0.2 one cannot turn off design mode if a > problem is detected in the query. Hmmm ... if you're changing the aliases only, there should be no problem in the query, should it? > Table joins are also affected and it appears that one cannot select > join types other than inner join in OOo1.0.2 and OOo1.1 beta what exactly cdoes "cannot select" mean? Is the option not offered? Is it offered, but does not persist when selected? Something else?
> > However, from OpenOffice 1.0.2 one cannot turn off design mode if a > > problem is detected in the query. > Hmmm ... if you're changing the aliases only, there should be no > problem in the query, should it? Hmm, again. ignore this sentence, please :), I misunderstood you in the first run ...
> However, from OpenOffice 1.0.2 one cannot turn off design mode if a > problem is detected in the query. Ian, are you saying that when you try to swicth from the graphical to SQL view, we already send the statement to the server? If so, this is a bug. Switching views should at most do some parsing, and we didn't yet find conditions under which we do more than this ...
Hi Frank Apologies for categorising this issue under spreadsheets - finger trouble >Ian, are you saying that when you try to swicth from the graphical to >SQL view, we already send the statement to the server? If so, this is a bug. Switching views should at >most do some parsing, and we didn't yet find conditions under which we do more than this ... Ok, I don't think I can confirm that the server is/is not being accessed when switching Design View off, although it appears that some check is made that the table exists. But I have a "gut" feel that the problem relates to the aliasing issue. For example: If I create the query in Design View and try to run the query (without switching design view off), a "long" error message from the ODBC driver and database manager is returned. The error indicates a problem with the table name; which is a valid table - this is the alias problem which I experienced with earlier versions of StarOffice and OOo and could resolve by editing the query. This not exactly the same as creating the query in Design View and switching Design View off before running the query, i.e. the error does not appear to originate from the the ODBC driver or database manager - so OOo probably already has some info about the tables to do some validation and UniVerse has not provided what is required because it wants aliases that are unique - just guessing :) Below are the steps I followed that result in the error when attempting to switch out from Design View after creating the query in Design View as well as creating the query directly as SQL. From within spreadsheet Select Tools\Data Source. Clicked New query in Design View Clicked a table to query Clicked columns to include in query Clicked Switch Design View On/Off (ie off) Received error message: "Error while connecting to the Data Source Table CUSTOMER_MASTER" is unknown in the data base" Clicked on MORE The table "CUSTOMER_MASTER" is unknown in the database These messages do no appear to be from the ODBC driver or database manager. The view does not switch from Design Mode to SQL entry. Then, Deleted the table from the Design View query Switched Design View Off Entered the query "SELECT AT_ID,OFFICE FROM CUSTOMER_MASTER" Executed the query - this returned data to the beamer Switched Design View "on" - this switched back to design mode with table and data visible Tried to switch Design View off Received error message as before "Error while connecting to the Data Source Table CUSTOMER_MASTER" is unknown in the data base" Clicked on MORE The table "CUSTOMER_MASTER" is unknown in the database At this point it was not possible to return to directly enter the SQL in Design View off without deleting the table/query and starting from scratch. Regarding the joins (I'll be more "selective" when using the term "select" :)" > what exactly cdoes "cannot select" mean? Is the option not offered? Is it offered, but does not persist > when selected? Something else? In OOo1.1beta, right-click on join line, click edit. On the display of join options only Inner Join option appears in drop-down, i.e. other options are not listed. Many thanks for responding to this query so quickly. Regards Ian
MSC->OJ: For OOo 2.0 maybe we can add a option for this behaviour.
for better finding of this issue, changing subject from ODBC - Tables aliased to same name in query by OOo rejected by database server to [RFE] don't general table aliases if not necessary Addittionally confirming this issue.
typo in title corrected
Started for OOo 2.0. in oj4
Fixed in cws oj4
Send to QA
Please have a look at http://dba.openoffice.org/howto/AppendTableAlias.html
fixed
set to fixed
set to verified
change subcomponent to 'none'
Hi, fixed in current developer build -> close. The current developer build can be found at http://download.openoffice.org/680/index.html Feel free to reopen if this issue is not fixed in the developer build. Bye Marc