Re: getting stomped on ORDER BY

On 11 Sep 2013 15:02, James H. H. Lampert wrote:

On 9/11/13 2:32 PM, Tommy.Holden wrote:

You can't use ORDER BY in a view. You'll have to add the ORDER BY
on your SELECT statement.

. . . or do a DDS logical file instead. In SQL, a VIEW and an INDEX
are completely separate entities.

FWiW: A CRTLF for a DDS keyed LF is not helpful to directly replace the VIEW as defined in the OP. Such a database file can not be created, because the ORDER BY included a column from a secondary file in the join.

Yet even if the file could be created to imply the collation, the OP would be restricted to using the RLA vs the SQL access in order to take advantage; i.e. an ORDER BY would still be required in any query of the file, to ensure the collation of the result set is predictable.

Plus, if a DDS keyed LF could suffice [e.g. non-join], then there would be little need to use DDS when the SQL DDL to CREATE INDEX could probably effect a keyed Access Path sufficiently similar to whatever the CRTLF might.