Issue 113251

Summary:	Must be able to use Run SQL Command Directly in all views
Product:	Base	Reporter:	osavill <osavill>
Component:	code	Assignee:	AOO issues mailing list <issues>
Status:	CONFIRMED ---	QA Contact:
Severity:	Trivial
Priority:	P5 (lowest)	CC:	drewjensen.inbox, issues, r4zoli
Version:	OOO330m1	Keywords:	needmoreinfo, oooqa
Target Milestone:	---
Hardware:	All
OS:	All
Issue Type:	DEFECT	Latest Confirmation in:	---
Developer Difficulty:	---

Description osavill 2010-07-19 15:40:58 UTC

The Run SQL Command Directly is only available when editing SQL queries.
However, these same SQL queries are available for use in Forms and Reports where
they fail because OOo forces syntax checking in these views. I have only used
MySQL and SQL Server with OOo and these both use some SQL which is non ANSI (and
I am not an SQL guru so I cannot re-write them in ANSI SQL!). There really must
be a Run SQL Command Directly in Forms and Reports.

Comment 1 r4zoli 2010-07-21 12:17:06 UTC

Run SQL directly used only in SQL View.
When you design query with UI, then you can switch to SQL View and use SQL
directly mode.

Where in forms you can not use run directly in SQL Designer View?
Where in reports you can not use run directly in SQL Designer View?

Please describe more detailed.

Please add example file where you can explain this problem.

Comment 2 osavill 2010-07-21 13:34:27 UTC

Hmmm, ok the queries appear to run okay in Forms so I must have been confused at
that point.

However, running a Report displays, for example:
The query with the statement
'SELECT DISTINCT "c1"."contact" AS "Name", CONVERT(char(11),"ch"."ondate",109)
AS "Pack 1 Sent", 

(SELECT DISTINCT CONVERT(char(11),"ch"."ondate",109)
FROM "contact1" AS "c2" 
INNER JOIN "CONTHIST" AS "ch" ON ("ch"."accountno" = "c1"."accountno" AND
"ch"."ref" LIKE 'Depression Article & Special Offer on Omega-3 EPA Vegepa
Capsules%') AND "ch"."ref" <> ''
WHERE "c1"."source" LIKE '%depression%' AND ("c2"."contact" = "c1"."contact"))
AS "Pack 2 Sent",

(SELECT DISTINCT CONVERT(char(11),"ch"."ondate",109)
FROM "contact1" AS "c2" 
INNER JOIN "CONTHIST" AS "ch" ON ("ch"."accountno" = "c1"."accountno" AND
"ch"."ref" LIKE 'Inspirational story from manic depressive Helen McNallen%') AND
"ch"."ref" <> ''
WHERE "c1"."source" LIKE '%depression%' AND ("c2"."contact" = "c1"."contact"))
AS "Pack 3 Sent",

(SELECT DISTINCT CONVERT(char(11),"ch"."ondate",109)
FROM "contact1" AS "c2" 
INNER JOIN "CONTHIST" AS "ch" ON ("ch"."accountno" = "c1"."accountno" AND
"ch"."ref" LIKE 'Depression Help Pack - The Importance of Exercise%') AND
"ch"."ref" <> ''
WHERE "c1"."source" LIKE '%depression%' AND ("c2"."contact" = "c1"."contact"))
AS "Pack 4 Sent",


(SELECT MAX("cs"."TITLE") FROM "CONTSUPP" AS "cs" WHERE "accountno" =
"c1"."accountno") AS "Last Order Date"

FROM "contact1" AS "c1" 
INNER JOIN "CONTHIST" AS "ch" ON ("ch"."accountno" = "c1"."accountno" AND
"ch"."ref" LIKE 'Depression Help Pack - thank you for downloading%') AND
"ch"."ref" <> ''
WHERE "c1"."source" LIKE '%depression%'
GROUP BY "ch"."ref", "ch"."ONDATE", "c1"."ACCOUNTNO", "c1"."CONTACT", "c1"."SOURCE"
ORDER BY "c1"."contact" ASC'
could not be run.
Check your data source.

However, these same queries run from the Queries and Forms sections.

Comment 3 r4zoli 2010-07-21 14:21:49 UTC

The "old" type or report wizard, not updated long time.
In the future will be replaced by report builder, developed as extensions:
http://extensions.services.openoffice.org/en/project/reportdesign

Maybe in this report designer more query will run, if they run in queries.

Comment 4 drewjensen.inbox 2010-08-30 03:29:14 UTC

Confirmed with 3.2.1 - whether there is resources to fix or not it is still an
issue, IMO - taking FS's advice setting to NEW and target ooLater

Comment 5 ocke.janssen 2011-02-03 09:29:29 UTC

Change prio.