Oracle supports "ORDER BY" with views, but DB2 doesn't.
When db2views.db2 generated by IDMT is issued against DB2, it causes the following error if "ORDER BY" is specified with views.
SQL20211N The specification ORDER BY or FETCH FIRST n ROWS ONLY is invalid.
So please drop "ORDER BY" from views, and log this action.
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
2 replies Latest Post - 2011-11-27T14:04:27Z by mohkawa
Pinned topic IDMT: views with "ORDER BY"
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2011-11-27T14:04:27Z at 2011-11-27T14:04:27Z by mohkawa
SystemAdmin 110000D4XK708 PostsACCEPTED ANSWER
Re: IDMT: views with "ORDER BY"2011-11-25T18:52:55Z in response to mohkawaDB2 only limits the ORDER BY from being in the outer most SELECT of the view.
So a simple work around is to add another select:
CREATE OR REPLACE VIEW v1 AS SELECT c1 FROM T ORDER BY c2
CREATE OR REPLACE VIEW v1 AS SELECT * FROM (SELECT c1 FROM T ORDER BY c2)