IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

Illegal use of keyword fetch. Token with union except query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
HABBIE

New User


Joined: 08 Jul 2014
Posts: 24
Location: INDIA

PostPosted: Fri Aug 28, 2015 5:20 pm
Reply with quote

Hi,


I m trying to write a query,

INSERT INTO ERT_TY
(z_DB_NAME,z_TABLE_NAME,z_COLUMN_NAME,S_COLUMN_TYP E,z_DATA)
SELECT 'TRAIN12','ALBUM',
'DEPTNAME','CHAR',DEPTNAME
FROM TRAIN12.DEPT FETCH FIRST 5 ROWS ONLY;

Error:
ILLEGAL USE OF KEYWORD FETCH. TOKEN WITH UNION EXCEPT QUERYNO WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601

My db2 : version8 Z/os
os: windows

From the docs I got that FETCH will not work inside a subquery.

Can some one kindly let me know , how i have to insert the first 5 rows of a table into some other table.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2508
Location: NY,USA

PostPosted: Fri Aug 28, 2015 8:14 pm
Reply with quote

Look for a Cursor.
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Fri Aug 28, 2015 9:50 pm
Reply with quote

How do you identify the 'first 5 rows' of a table?

Do you have any specific records to be inserted into the table OR you just want some random 5 rows from another table?

If you want specific rows then you can use a 'where' clause in your select query. Otherwise, you may have to write something like:

Code:
INSERT INTO ERT_TY
(z_DB_NAME,z_TABLE_NAME,z_COLUMN_NAME,S_COLUMN_TYP E,z_DATA)
SELECT 'TRAIN12','ALBUM',
'DEPTNAME','CHAR',DEPTNAME
FROM TRAIN12.DEPT where condition UNION ALL
SELECT 'TRAIN12','ALBUM',
'DEPTNAME','CHAR',DEPTNAME
FROM TRAIN12.DEPT where condition;


Repeat the Select query 5 times with Union All. So, there will be 5 select queries and each query should return one row to be added. I don't see if you can actually insert a fixed number of rows through a Select query in a single shot.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Fetch the Dataset names from inside m... TSO/ISPF 18
No new posts How to fetch the latest record from a... DFSORT/ICETOOL 8
No new posts Mainframe ISPREDIT Macro query TSO/ISPF 3
No new posts DFSORT Output file order query DFSORT/ICETOOL 2
No new posts To fetch value separated by commas fr... DB2 3

Back to Top