FETCH FIRST nn ROWS fails on UDB Insert

[matchum]

I have a command table containing:
---********************************---
connect to sample;
drop table emp1;
create table emp1 like employee;
insert into emp1 select * from employee
-- fetch first 5 rows
;
select count(*) from emp1;
connect reset;
---********************************---

This will successfully create table EMP1 with 32 rows in it (the same rows in the distriuted EMPLOYEE table in the SAMPLE d/b). However, by uncommenting the 5th line, I would expect only 5 rows to be inserted.

The "FETCH FIRST nn ROWS" certainly works as a stand-alone Select. Why does it not work when combined with the Insert command ?

 

[stoggers]

Hi,

Following the INSERT (and a few others - CREATE VIEW etc) the syntax will only allow a subselect and not a full select. For this reason the FETCH FIRST n ROWS is not allowed.

It is possible to simulate this however by using the ROW_NUMBER function.

Regards,

Stoggers.

 

[matchum]

Stoggers,

Thanks for that but ROW_NUMBER assumes ODBC-base (unless I misread the manual). I will examine it in more detail tomorrow.

In the mean time, if you have any method for quickly allowing the above command file (command.txt) to be driven via (say) db2 -tvf command.txt, then I would be very grateful.


Thanks