--CELKO-- wrote:
I am working on the third edition of SQL FOR SMARTIES. If anyone has
an SQL programming technique, trick or tip that they think should be
in the book, drop me an email.
You get a virtual beer and your name in the book. Even better, if
you have an improvement on something in the second edition, you get two
virtual beers and a chance to humilate me in my own book!
Code should Standard SQL-92 or SQL-99, but if you do not know
Standard SQL, then use vendor code that maps into Standard SQL (the most
common example would be temporal functions) or it should be portable (i.e.
The MOD() function is not part of SQL-92, but every SQL product has some
version of it).
I'm curious to know how positioning and scrolling in a set can be done
in SQL (and in commercial products) in particular when 1 table is
involved with logical keys composed of multiple columns.
I see 2 components for it and a hole in the SQL standard.
The first component: select ... from y,z, t
can have where, order by, group, ...
The second component: the cursor: declare cursor for <component 1>
Finally: open cursor.
One of the basic, often occuring subset is
select ... from X where ... order by ...
So with 1 table involved, a where clause, an order.
SQL-92 has a row-value constructor clause that allows to write:
select ... from X where (c1,c2,c3, ...) <operator>
("C1","C2","...", ...)
which allows to define a where clause on composite logical keys
(multiple columns). Add an index on the logical keys and the row-value
constructor matches nicely the index when 1 table is involved. But
row-value constructors apply to a more elaborate set also (select on
multiple tables).
On the cursor side the SQL standard was not consistent in its
evolution..
In the SQL-.. standard (<SQL-92) cursors at open are always at the
beginning of the set. It makes sense as only fetch forward was
possible.
In SQL-92 scrolling cursors were introduced but the standard commitee
did not change the initial cursor positioning: as a cursor can go up
and down, there is no reason the cursor should be at the beginning of
the set at open. Cursor positioning at open should be totally
independent of set composition and the "first" row in the set.
So the syntax could be: open cursor position cursor where ... and to
use row value constructors again: open cursor position cursor where
(c1, c2,...) <operator> ....
So my question is: how to solve this problem in SQL and SQL
dialects/extensions?
Bernard Dhooghe
PS: DB2 has generated columns which allows to make one physical column
of several columns. Drives me ...SQL-92 is the (partial) solution.
