Topic
  • 2 replies
  • Latest Post - ‏2011-11-27T14:04:27Z by mohkawa
mohkawa
mohkawa
9 Posts

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

    Re: IDMT: views with "ORDER BY"

    ‏2011-11-25T18:52:55Z  
    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

    Re: IDMT: views with "ORDER BY"

    ‏2011-11-27T14:04:27Z  
    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
    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.