Issue 48116

Summary: Support SubSelects in Query Design View
Product: Base Reporter: demolish <fraser_donald>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues
Version: OOo 1.1.4   
Target Milestone: ---   
Hardware: All   
OS: Windows 2000   
Issue Type: ENHANCEMENT Latest Confirmation in: ---
Developer Difficulty: ---

Description demolish 2005-04-25 14:22:39 UTC
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
Comment 1 marc.neumann 2005-05-24 13:59:29 UTC
Hi,

The main problem is that the design view doesn't support sub selects, so first
we need an enhancement that the query design view supports subselects. If this
feature is added to the design view this issue should be fixed. So I reassign
this issue to the User Experience Team for evaluating the sub select feature.

Bye Marc
Comment 2 drewjensen.inbox 2006-04-20 10:28:21 UTC
Supporting sub selects uding the query design view is really another way of
saying that a query should be able to use another query object as a table source.

However, that said IMO this is not the only issue with this issue. Perhaps I
should say it is not the only solution to the users problem.

If the query designer could remember which view was open on last save, (designer
yes/no) then open in that view there would not be a problem either. A user can
force this to happen by selecting 'Run SQL Direct', then the designer will not
be displayed when reopened. But this has other consequences, so is not an
acceptable work around.