Re: [dba-users] Queries in Queries

[Andrew Jensen]

Frank Schönheit - Sun Microsystems Germany wrote: Hi Andrew, If a query is just a container for a select statment, then the parser could use that named container in its peresentation to the user. At runtime however, prior to issuing the statement to the engine the container would be expanded to the select statement it holds. Ah - this was in fact how I first understood it, but then, after reading you mail again, I though you meant it the other way round. Which made me rewrite my answer I already had written. But I, I saved it :) The problem I have with this approach: It requires constant extension of the parser. Let's be honest: OOo's parser currently lacks some features which state-of-the-art engines support. Said carefully. Implementing sub-selects in a way that the user enters SELECT * FROM <query> where OOo replaces <query> with the actual SELECT statement of <query> requires that OOo can parse the "outer" query. That's fine, to a certain extent. However, in some (well, too many) cases people want to use backend-specific syntax in their outer query. Say, it looks like SELECT * FROM <query> WHERE <field_1> = <function>( <field_2> ) where <function> denotes a function understood by the backend, but not by OOo. In this case, we usually tell the user to switch on the "Execute statement directly" button. That's also fine - except that now the user would lose the "queries in queries" feature, since now OOo doesn't have a chance anymore to replace <query> with the subselect statement. So, in consequence, OOo would have to _constantly_ extend its parser to know everything which people might want to use in a query using another query. This might be reasonable (though not really scale) for missing SQL-92 features of the parser, but certainly not for every backend-specific thingie people may insist to use in their queries. Ciao Frank Frank, I don't understand this argument. One could say the same thing now. Right now people are bumping their heads on this limitation - the lack of support for most functions and strored rpocedures via the query builder - not to mention missing SQL92 constructs. ( In fairness only the || comes to mind ). If you take your reason, that this limitation does not allowi this approach to supporting queries in queries, then you shouldn't even support single select statements in queries. However, if you define part of the requirement as: Any query that can be run with escape processing set to true may be used as a table source within a query. Any query that uses another query as a table source must be able to run with escape processing set to true. Then the issue just melts away. The parser is going to have to expand to understand more and more functions and procedures anyway- the user base is going to insist on it. If the requirement where met as I stated then as these functions and procedures are added the parser needs not be changed any further just to support this query in query feathre.