kent@manzi.unx.sas.com (Paul Kent) (06/28/90)
In article <910@nlsun1.oracle.nl> bengsig@oracle.nl (Bjorn Engsig) writes: >It should be noted that the problems are in the definition of SQL itself and >not with its various implementations. SQL is not a very well designed >language, which has been pointed out very elegantly by C.J.Date in his >"Selected Writings", plublished by Addison Wesley. One of his mainpoints >about good language design is that it should posess 'orthogonality', which >at some points is the same as the term 'recursively' used in the quotations >above. If SQL was defined better, a 'table' would be a 'table expression', >and it could be used everywhere a table is allowed as it is now, e.g.: > >select sum(a) from > ( select y, avg(z) a > from zz > group by y ) > >where today you would have to define the inner select as a view and then use >that in the outer select. Please note, that the problems are in the syntax >not directly allowing what the current implementations actually can do >semantically. I know that full blown SQL2 implementations are light years away, but some of the language features are being adopted by SQL processors in an incremental fashion. Why not generalise Bjorn's "table" to be "query expression". After all, you might wanna say select sum(a) from ( select avg(z) AS a from zz group by y UNION select avg(z) from zz2 group by y ); There is no need to select the grouping variable if all you needed it for was the grouping in the first place. In the SQL implementation that i am most familiar with (PROC SQL for SAS) we support these query_expressions -- we called them "in-line views" before it was fashionable to do recursive queries... I believe that there are no queries that you can pose using a view that you cannot pose using an inline specification, but i have no formal proof. An interesting outgrowth of all the orthogonalisation is that query expressions that return a "scalar" result can be used at any point you might have used a constant, making queries like this possible... (You no longer have to teach people to invert the sense of "where the count of thing for this group is greater than zero" into "where zero is less/equal to the count of thing for this group" to formulate a subquery.... ie, both these queries are valid SQL2 (provided the subquery returns just ONE row for each distinct value of the correlation variable). select x, (select foo from bar where outer.x = bar.x) from table outer; select x from table outer where (select count(thing) from othertab where group=outer.group) > 0; except that OUTER is a new "keyword", and all keywords are reserved by the standard. (oh, you have a column called "CASE" -- thats reserved too) my biggest gripe with SQL2 (after the size of it) is the wild abandon that it introduces new keywords with. Some implementations will not require reserving these words for their parsers to operate successfully, but if you use em in your queries, its gonna be harder to move that application around. So does anyone else out there care about SQL2??? Paul. -- ---- nothing ventured, nothing disclaimed ---- paul kent, SAS Institute, box 8000, cary nc 27512-8000 -- 919 677 8000 ... kent@sas.com -- or perhaps -- {seismo|mcnc}!rti!sas!kent