Created attachment 38943 [details]
database with query

I don't grasp your suggestions, sorry :-\
When executing such a query, the "More" button in the error message should lead
you to a "Information" entry telling you the statement which was executed. Not
sure if this goes into the direction you mean.

Other than this, the behavior is, well, by design. So allowing QiQ for native
queries would be an ENHANCEMENT. Chaning the error messages to better help the
users would be easier, but I'm not sure about a good way to do this.

"So allowing QiQ for native queries would be an ENHANCEMENT."

That is not my point at all. What I am saying is that when the user selects 'Run
SQl Direct' that the QiQ query definition be converted into the actual 'native'
SQL statment that would have been sent to the engine if the 'Run SQL Direct'
button where not engaged.

So, in my example of Query1 :

SELECT "abc".*, "level 1".* FROM "level 1", "abc" WHERE ( "level 1"."ID" =
"abc"."ID" )

would become

SELECT "Level 1".*, "ABC"* FROM "abc", ( SELECT * FROM "abc" ) AS "Level 1"
WHERE ( "level 1"."ID" = "abc"."ID" )

There is already code to handle the transformation, it would just be a matter of
runnig the query definition through this section and replacing the text of the
query definiton with the out put.

I realize that this would most likely need to be a one way trip so perhaps the
user should be prompted before doing it. Or perhaps it could offer to do this
'into' a new query definition.

There are two cases where it seems to me this would be a valuable feature.
1] The user wants to add some construct or funciton available with a specific
database engine but not recognized by the OOo query parser, to an existing QiQ
query definition. 

2] Troubleshooting. For an example looking at Issue #69227, if one replaces the
QiQ query definition Level 3, with the actual SQL statment that would be created
by simple substitution and then pass this directly to the engine you get a
completely different error then the one reported. [ Column already exists in
Statement.... ] This error message actually gives someone enough information to
let them fix the problem. 

thanks for the explanation, now I get you ...

Though, not sure about this. "Run SQL Direct" means: "don't touch what I did".
Replacing sub queries is not the most consistent thing then.
On the other hand, some help for the user to resolve the problem would probably
be a Good Thing (TM). Personally, I'd probably prefer a more explicit option -
"Substitute sub queries" or so - over a silent analysis/magic "Hi, here's your
personal Base pet! It seems you're trying to ... Should I ...?" - you know, this
kind of annoying "assistant" :)

"I'd probably prefer a more explicit option"

Thinking further about using the 'SQL Direct' toolbutton here, and reading your
responses, perhaps this is not be a good idea because: it makes one control do
two completely different functions, depending on context. At the moment this
effects the 'mode' or more precisely, if I understand what is happening, it is
just the GUI manifestation for the 'Escape Processing' property on the result set. 

Well, there are two 'views' available now from the menu and toolbar. Designer
view and SQL View. OOo already uses context sensitive menus and toolbars
extensively, so introducing another should not throw the users off. What about a
new menu and toolbutton that would change the 'view' again. Not sure about a
name, SQL Native or SQL Raw or SQL Direct. I don't know.

Basically it would be promoting, if you will, the function of the 'SQL Direct'
toolbutton to managing a 'view' of the query definition. A menu item could be
added then to the 'View' menu also. This new 'view' being mutually exclusive to
the others.

Taking this type of approach then it also negates the possibility of creating a
QiQ definition, then selecting 'SQL direct' mode, as is possible now, which can
only result in an error.

Finally, I wonder if this then would merit a new task item in the Base query
window, 'Create query in SQL direct view...'?

To confirm I understand your properly:

We would have 3 views then
- "Design Mode"
- "SQL Mode"
- "SQL Direct Mode"
where the latter would supersede the current "Run SQL Direct" setting.

The third mode would be disabled (or raise an error when selected) if the query
is based on another query.
OR
The third mode would show the substituted statement (i.e. the statement with
queries replaced with their constituting statements).

If the latter, what would happen if the user would change the statement in the
"SQL Direct Mode"? There would hardly be a way back to the other modes then.

Also, wouldn't this be confusing for novice users, which are confronted with an
"SQL Mode" and an "SQL Direct Mode" which seem to be the same (as long as they
didn't add a query)? (Well, at least more confusing than today's "Run SQL
Direct" button.)


Doesn't sound too convincing to me, but perhaps I just didn't yet get the
complete picture.

Well, when I read your re-statement of the idea. ( and yes I think you got what
I meant ) The first word that comes to my mind is, muddled.

Let me just put it this way then:

There should be some way for the user to see the actual SQL statement that would
be issued to the engine, for QiQ query definitions.

If this "way", what ever that is, also allowed for this statement to be copied
and pasted into a new query definition so that case 1 above could be handled,
all the better. Case 2 - troubleshooting is however the more important, IMO.

sounds heavily like a feature enhancement than like a bug -> so i send this
issues as a 'feature anhancement' to the 'requirements' team - correct me if i
am wrong