Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: Sql server to Db2 -- select top 100 percent

    Hi friends,

    Could any one help me out on migrating below query to Db2?..


    create view ipm_booking_view_pass as
    select top 100 percent b.*, p.passenger_unique_key, p.pass_fname, p.pass_lname
    from ipm_booking_view b left outer join ipm_booking_passengers p on b.oid = p.oid
    order by b.store_id, b.booking_id, p.passenger_unique_key
    GO

    Note:- I am using DB2 9.7

    Thanks

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    top 100 percent <> All?

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    Hi,
    Yes, i need it like the way it works in sql sever
    thanks

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    in that case wouldn't you just remove the TOP 100 percent?
    Dave

  5. #5
    Join Date
    Apr 2010
    Posts
    3
    Hi Dave,

    I got error as below after removing top 100 percent.

    create view ipm_booking_view_pass as select b.*, p.passenger_unique_key, p.pass_fname, p.pass_lname from ipm_booking_view b left outer join ipm_booking_passengers p on b.oid = p.oid order by b.store_id, b.booking_id, p.passenger_unique_key
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL20211N The specification ORDER BY or FETCH FIRST n ROWS ONLY is invalid.
    SQLSTATE=428FJ

    SQL20211N The specification ORDER BY or FETCH FIRST n ROWS ONLY is invalid.


    Explanation:

    An ORDER BY or FETCH FIRST n ROWS ONLY is not allowed in:
    * the outer fullselect of a view
    * the outer fullselect in the RETURN statement of an SQL Table function
    * a materialized query table definition
    * a subselect which is not enclosed in parenthesis

    User response:

    In case of:

    subselect
    Enclose the subselect including the ORDER BY or FETCH FIRST n
    ROWS ONLY in parenthesis.

    FETCH FIRST n ROWS ONLY
    Use the ROW_NUMBER() OVER() clause in conjunction with a
    predicate in the where clause. Example:
    SELECT name FROM
    (SELECT
    ROW_NUMBER() OVER() AS m, name
    FROM emp
    ) AS e
    WHERE m < 10


    ORDER BY Use ORDER BY in the query using the view, the materialized
    query table, or the SQL table function instead.

    sqlcode: -20211

    sqlstate: 428FJ

    thanks

  6. #6
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try listing the B tables columns out in your select statement.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •