To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=74071
Issue #|74071
Summary|2.1.0 Base table alias
Component|Database access
Version|OOo 2.1
Platform|PC
URL|
OS/Version|Windows XP
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|none
Assigned to|dbaneedsconfirm
Reported by|rjahn1
------- Additional comments from [EMAIL PROTECTED] Wed Jan 31 15:22:11 -0800
2007 -------
Table alias is not a cosmetic feature. It is mandatory to build complex queries
when the same source table is invoked multiple times. Without table alias,
complex queries cannot be built.
OO Base currently provides 2 types of stored queries.
* SQL View
created through CUI (Tools – SQL)
stored on the DB engine side
handled by DB engine
* GUI Query
created through GUI
stored on the client side
handled by Base GUI parser
GUI Query in 2.1.0 always fails when table alias is in query statement.
Example:
* Create a new database.
* Create 2 test tables, as shown in the end.
* Select GUI Query ("Queries" in the left pane)
* Select "Create Query in SQL Window"
Note: still inside Base GUI, not handled by DB engine
* type in the contrived query statement, as shown in the end.
* Press the design mode button (switch to GUI mode)
4 tables of alias names appear in relation window.
No tables of the original name
No key connection lines between any tables.
* Press the design mode button (switch to text mode)
Table aliases disappeared in the "From" clause.
Table aliases remain in other sections of the query statement.
* Press the design mode button (switch to GUI mode)
6 tables icons appear in relation window,
4 in alias names, 2 in the original names.
No key connection lines exist on the table icons of the original names.
Key connection lines appear correctly for table icons of alias names.
* Further switching between the GUI and text modes reveals no more changes.
* Save as "qq test" and close the GUI.
Should see a stored GUI Query "qq test".
* Double click "qq test" to execute.
* error message:
The data content could not be loaded.
Column not found: tm01."ID module" in ...
-- SQL code to reproduce the symptom
Create Table "tt part"
(
"ID part" BigInt,
"source" VarChar( 100 ),
"notes" VarChar( 100 ),
Constraint pk_tt_part Primary Key ( "ID part" )
);
Create Table "tt module"
(
"ID module" BigInt,
"ID part" BigInt,
"notes" VarChar( 100 ),
Constraint pk_tt_module Primary Key ( "ID module" ),
Constraint fk_01_tt_module
Foreign Key ( "ID part" )
References "tt part" ( "ID part" )
On Update Cascade
On Delete Restrict
);
-- a trivial, contrived query for table alias
Select tm01."ID module" as md01, tp01."ID part" as pt01,
tm02."ID module" as md02, tp02."ID part" as pt02
>From "tt module" as tm01, "tt part" as tp01,
"tt module" as tm02, "tt part" as tp02
Where tp01."source" = 'vendor 1' and tp01."ID part" = tm01."ID part" and
tp02."source" = 'vendor 2' and tp02."ID part" = tm02."ID part";
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
