To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=48116
Issue #:|48116
Summary:|SQL Query "Design View" fails but "SQL View" ok
Component:|Database access
Version:|OOo 1.1.4
Platform:|All
URL:|
OS/Version:|Windows 2000
Status:|UNCONFIRMED
Status whiteboard:|
Keywords:|
Resolution:|
Issue type:|DEFECT
Priority:|P3
Subcomponent:|none
Assigned to:|msc
Reported by:|demolish
------- Additional comments from [EMAIL PROTECTED] Mon Apr 25 06:22:39 -0700
2005 -------
I enter the following SQL statement into the "SQL View" editor:
SELECT (SELECT "tbl_sectype"."s_desc" FROM "public"."tbl_sectype" "tbl_sectype"
WHERE ("tbl_sectype"."id" = "tbl_dol"."id_sectype")) AS "security type",
count(*) AS "cnt" FROM "public"."tbl_dol" "tbl_dol" WHERE
("tbl_dol"."id_securrency" IN ( SELECT "tbl_dol2"."id_securrency" FROM
"public"."tbl_dol" "tbl_dol2" WHERE ("tbl_dol2"."b_notondol" = 'false' AND
"tbl_dol2"."d_2list" >= '1999-01-01' AND "tbl_dol2"."d_2list" <= '1999-12-31')
GROUP BY "tbl_dol2"."id_securrency" )) GROUP BY "tbl_dol"."id_sectype" ORDER BY
"tbl_dol"."id_sectype"
I can run the query and it returns the expected result set. I can save the query
with a name and later use this in a report with expected results.
When I try to edit this query the editor always starts in "Design View" - I do
not have the option to start editing in "SQL View" and without editing anything,
when I try to switch to "SQL View" I get the following error:
"Error while connecting to the data source
SQL syntax error"
I have tried two different data sources, a PostgreSQL ODBC connection and the
native postgresql OpenOffice driver. I get the same error regardless of the data
source, I therefore believe this is an internal error created by the "Design
View" when it attempts to re-create the SQL statement.
The table definitions are very simple and as follows:
CREATE TABLE tbl_sectype
(
id int4 NOT NULL,
s_desc text NOT NULL,
CONSTRAINT tbl_sectype_pkey PRIMARY KEY (id),
CONSTRAINT tbl_sectype_desc_key UNIQUE (s_desc)
)
CREATE TABLE tbl_dol
(
id int4 NOT NULL,
id_securrency int4 NOT NULL,
id_sectype int4 NOT NULL,
d_2list date NOT NULL,
d_2delist date,
b_notondol bool NOT NULL,
CONSTRAINT tbl_dol_pkey PRIMARY KEY (id),
CONSTRAINT fkey_sectype FOREIGN KEY (id_sectype) REFERENCES tbl_sectype (id)
ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
)
Now you might say - well why don't you save it with the option "Run SQL command
directly" set to true. This would be just a work around, I actually want this
query to run as a parameter query which it cannot do in that mode. I tried to
set the date column "d_2list" as a parameter but then the query fails. This is
obviously another issue all together, but I believe until this one is fixed I
cannot raise this issue as its probably related.
Regards
Donald Fraser
---------------------------------------------------------------------
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]
