How can i use order by in inner query of a nested query or view

[prasoonkc]

Hi I am working on a project where we have to migrate the database from oracle to db2. My question is can i use order by clause in a nested query i,e the inner query contains order by clause. Db2 is giving me error when i use order by in the inner query of a nested query. Also it does not allow me to use order by when i am creating a View. Am I missing some thing here please advice. The view is as follows

CREATE VIEW RANDOM_DRIVER_VIEW AS
SELECT RAND(DAY(CURRENT DATE)+MONTH(CURRENT DATE)*100+YEAR(CURRENT DATE)*10000+MICROSECOND(CURRENT TIMESTAMP)+SECOND(CURRENT TIMESTAMP)+INT(NU_DRIVER_ID)) RANDOM_ORDER,
DQDS_DRIVER.* FROM DQDS_DRIVER ORDER BY RANDOM_ORDER

if i remove the 'ORDER BY RANDOM_ORDER' by part it works also if i run the query with out 'CREATE VIEW RANDOM_DRIVER_VIEW AS' part then also it works. Is there a work around. Oracle does allow the order by in nested query and views.

 

[Hattusas]

Something to ask:Why do you need this?You can still use order by syntax while accessing your view.
e.g suppose we have a view named as test.view
"Select field_a,field_b from test.view order by field_a" will work.
Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[legacy370]

It's my understanding that you can't use an ORDER BY when defining a view. As Hattusas remarked, you're free to use the ORDER BY when accessing the view.