Topic
IC4NOTICE: 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.
2 replies Latest Post - ‏2011-11-27T14:04:27Z by mohkawa
mohkawa
mohkawa
9 Posts
ACCEPTED ANSWER

Pinned topic IDMT: views with "ORDER BY"

‏2011-11-07T23:56:02Z |
Hi,

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.

Thanks,
Updated on 2011-11-27T14:04:27Z at 2011-11-27T14:04:27Z by mohkawa
  • SystemAdmin
    SystemAdmin
    708 Posts
    ACCEPTED ANSWER

    Re: IDMT: views with "ORDER BY"

    ‏2011-11-25T18:52:55Z  in response to mohkawa
    DB2 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)

    Cheers
    Serge
    • mohkawa
      mohkawa
      9 Posts
      ACCEPTED ANSWER

      Re: IDMT: views with "ORDER BY"

      ‏2011-11-27T14:04:27Z  in response to SystemAdmin
      Hi Serge,

      Thank you.

      Please let me make sure.
      Does the result assure "order by"?
      If yes, IDMT is better to say attention message with such solution.