Re: [dba-users] Queries in Queries

[Andrew Jensen]

Hello Frank (Except that when we do not implement "Queries in Queries" by "Queries in Views", then this means we probably won't invest the effort for the editable views :) Actually this sounds like a darn good add on for some one to work on. That developer article contest is going on again in May, isn't it? Yes, in fact currently HSQLDB enforces the integrity, so it is as you describe (well, except issue 64885 :( ). Ideally, it would be best to recognize the invalidity of a view at the very moment it is actually *used*, but so far I couldn't convince the HSQLDB project that this would be best from an OOo user's point of view ;) I can not agree with you on this point Frank. Views area known commodity in the RDBMS world. Insuring that they run, as expected, is a good thing. Well, this shouldn't be a problem. HSQLDB allows storing parametrized views, but currently OOo doesn't respect this in that it doesn't know You don't mean that HSQL has some native support for replaceable parameters do you? I did a quick search of their forums and documentation before my last message and couldn't find any mention of it, granted I did not expect to find any. I take it then you mean that one can store a string value as part of the select statement that represents the parameter?  Yes, you can do that now. Then OO could read this select statement before the engine creates the temporary table, prompt for the actual value and replace the string in the select statement, then hand this back to the engine. Sounds to me a bit like a $50.00 fix for a $5.00 problem, because as long as Base maintains a distinction between a client side Query and an engine side View there is no need for this, one need only derive a Query from a View and add the parameter(s). hmm, as I read that line I am not sure I can defend my cost / benefit analysis, but will let it stand for what ever it is worth.. :>) In general, I think the whole issue depends on the requirements. Yes! [ Frank, you are a master of understatement at times, and that can be a good thing for a project manager - of course product managers are usually the ultimate masters of the art :-D ] (Though I still don't like the fact that the whole "Queries in Queries" feature depends on the parsebility then :-\) A very real issue, not easily dismissed. It may be however that this is a case of blending issues together. The question of the query designers support for built in procedures and functions is an issue seperate from that of nested queries. It has an affect on the usablility of nested queries, for sure, but in and of itself does no seem to be a driving factor one way or the other regarding implementation, IMO. One reason I say this is, if nested query support is implemented by substitution then support for views is still available. Therefore if a person needs to use a query that can not be parsed by the designer they are still able to use the current procedure of converting this query to a view first and then using it. Further, once nested queries are available any improvements to the designer's parser in support of procedures, functions and extended SQL constructs should automatically expand the usability of the nested query feature and diminish the need for this 'create a view first' work around. This raises another point in my mind, product differentiation. ( but for this I will move back to the thread 'the products directions' ) One last item on this issue however. This from a Microsoft article titled 'Migrating Your MS Access Database to MS SQL Server 7.0', July 19, 2001. Migrating Microsoft Access Queries You must move your existing Access queries into SQL Server in one of these formats: •    Transact-SQL scripts ........ •    Stored procedures ........ •    Views .......... ..... ...... Nested Queries Some Access queries are created on top of other queries in a nested fashion. Nested queries in Access become nested views in SQL Server. The ORDER BY clauses cannot be part of a view definition but are appended to the SELECT statement that queries the VIEW. If you have nested Access queries, create several views, and then create stored procedures that both perform a SELECT operation on the view and append an ORDER BY clause to the SELECT statement. .... Conclusion Migrating an Access 2000 database to SQL Server is a process that requires using only the Upsizing Wizard. Upgrading without the wizard requires moving the data and transferring the queries to the appropriate form (SQL Server scripts, views, and stored procedures). >From another MS article - 'How to Migrate from Access to SQL Server 2000'. December 1, 2004. In situations where you have queries incorporating multiple table joins, a better solution would be to create a SQL Server view and modify your Access queries to request data from that view (instead of directly from the joined tables). Using a view, SQL Server performs any table joins and filtering on the server side, and only returns the filtered set of data, which can vastly improve network and system performance. OK, so all of that for this point. If Base can implement nested queries in such a way as to continue to be agnostic to the backend database then there is a real value added over MSA, for one should be able to start with a stand alone HSQL database and move as needed to say a MySQL database ( or HSQL server ) almost automatically.We have ofered the user the ease of MSA in building complex queries while retaining the power of the client/server model, whithout the user needing to become a dba themselves and possibly never needing to learn the term sub-select. In the case of Base queries, nested or otherwise, that use parameters they also should continue to work irregardless of the backend.