Re: SQL View

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

David

I believe it's in V5R2. You use a common-table-expression, which can have
an order by clause, as well as a fetch first n rows only clause, now.

So

create view libname/viewname (column1, coumn2, ....) as with temptable as
select field1, field2, ... from somelib/somefile order by field1 select *
from temptable

create view libname/viewname (column1, coumn2, ....) as select * from
temptable as select field1, field2, ... from somelib/somefile order by field1

or something like that.

So it's not in the definition of the view, it's in the definition of the
temp result table that the view is built over.  Heh! Heh!

See
<http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmsthcview.htm>.

Regards

Vern

At 03:07 PM 12/13/02 -0500, you wrote:
> Will our mighty iseries ever allow us to Create a SQL View with an Order
> By clause?  Anybody have any info on this? IBM?
>
> I did some searching on this and did not find much other than a design
> change request by Rob Berendt about a year ago and the response from IBM:
> "IBM agrees with the request and a solution appears to be a desirable
> objective. A solution however may not presently appear feasible or
> implementable. No IBM commitment is made or implied as to the eventual
> delivery of an acceptable solution."
>
> David Smith
> Tri-State Hospital Supply Corp.
> IT Consultant
> dsmith@tshsc.com
> 517.546.5400
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.