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

top 100 percent <> All?

Hi,
Yes, i need it like the way it works in sql sever
thanks

in that case wouldn't you just remove the TOP 100 percent?
Dave

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

Try listing the B tables columns out in your select statement.
Dave