|
Posted by Serge Rielau on 03/08/06 15:00
David Portas wrote:
> Views don't return any data. Only the queries issued against a view can
> return data to the client. That means either queries in a stored
> procedure or in a client application. So those queries would still have
> to be the place for your "ORDER BY _ORDER_OF_A_" clause.
Correct. The need for the query was there before. My proposal does not
change that.
> There is a problem. The predefined view order can only work if the
> column(s) referenced by _ORDER_OF_A_ are also referenced in the SELECT
> list of the *query* (not just the view). Otherwise it may not be
> possible to determine the correct order and perform the sort. For this
> reason, standard SQL (and other SQLs too in many cases) will raise an
> error if the ORDER BY columns aren't also included in the SELECT list.
I don't think this is the case in standard SQL (but I don't have the
time to dig through it now. FWIW DB2 does not require the columns to be
specified in the select list.
> The implication is that the person writing the _ORDER_OF_A _ clause in
> the query or proc must A) know which columns determine the order, and
> B) include those columns in the SELECT list. So given those constraints
> why wouldn't he or she just type the column names in the ORDER BY list
> as well?
Without relaxing the limitation you encountering that would be correct
and indeed undesirable. Given that views get expanded into the query
there is no technical reason for the presence of the order by columns in
the view signature (the columns exposed by the view).
I would guess that SQL Server must know the internal concept of
invisible columns (such as row-id) which is used to pull through order
without infesting the exposed select list.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Navigation:
[Reply to this message]
|