Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
dandapani

New User

Joined: 13 Dec 2005
Posts: 7
Location: banglore

 Posted: Wed Dec 14, 2005 7:44 pm    Post subject: selecting records from 20 to 30 in a table Hi , Need query for selecting records from 20 to 30 rows in a table . It would be a great ,if you could post queries as soon as possible. Regards, Dhans

iknow

Active User

Joined: 22 Aug 2005
Posts: 413

Posted: Wed Dec 14, 2005 9:00 pm    Post subject: Re: selecting records from 20 to 30 in a table

Hi dandapani,

NOTE

Queries are tested using ORACLE and SQL. In certain cases some queries won't work properly. So apply the same logic in DB2.

Solution1:

Retrieving only rows X to Y from a table

In your case x=20,y=30.

 Code: SELECT * FROM (        SELECT ename, rownum rn               FROM emp WHERE rownum < 31     ) WHERE  RN between 21 and 30 ;

Note: the 31 is just one greater than the maximum row of the required rows (means x= 20, y=30, so the inner values is y+1).

Solution2:
 Code: SELECT rownum, f1 FROM t1    GROUP BY rownum, f1 HAVING rownum BETWEEN 21 AND 30;

Solution3:

Another solution is to use the MINUS operation. For example, to display rows 20 to 30, construct a query like this:

 Code: SELECT *         FROM   tableX         WHERE  rowid in (            SELECT rowid FROM tableX            WHERE rownum <= 30           MINUS            SELECT rowid FROM tableX            WHERE rownum < 20);

Solution4:

"This one is faster and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."

 Code: SELECT *      FROM (SELECT a.*, rownum RN         FROM (SELECT *                           FROM t1 ORDER BY key_column) a             WHERE rownum <=30)     WHERE rn >=20

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

Hope this helps.
dandapani

New User

Joined: 13 Dec 2005
Posts: 7
Location: banglore

 Posted: Thu Dec 15, 2005 2:53 pm    Post subject: Thanks for posting your comments Hi Iknow , I don't thing so that pre-defined function rownum exixts in DB2 .... We tried , its giving error not a valid function. Please let me know if you have any other solution in DB2 Regards, Dhans.
iknow

Active User

Joined: 22 Aug 2005
Posts: 413

 Posted: Thu Dec 15, 2005 3:02 pm    Post subject: Re: selecting records from 20 to 30 in a table Hi dandapani, Try with ROWID instead of ROWNUM. I will try to give the solution to your query.
anilbatta

New User

Joined: 13 Nov 2005
Posts: 22

 Posted: Fri Dec 16, 2005 3:39 pm    Post subject: Hi The following is the query in which it extracts only 10 rows between 20 and 30 rows. SELECT * FROM STUDENT WHERE ROWID IN (SELECT ROWID FROM STUDENT WHERE ROWNUM <=30 MINUS SELECT ROWID FROM STUDENT WHERE ROWNUM >20); Try with this code and let me know if any questions.Today i tried with this code and it is the efficient code to retrieve the number of rows.
nikyojin

New User

Joined: 05 Oct 2005
Posts: 94

Posted: Tue Dec 27, 2005 10:35 am    Post subject: Re: selecting records from 20 to 30 in a table

Hi Anibatta,
I tried the same query with some modifications as mentioned below :-
 Quote: SELECT CR_STATUS_DATE FROM HUM.A_CRSTT WHERE ROWID IN (SELECT ROWID FROM HUM.A_CRSTT WHERE ROWNUM > 20 AND ROWNUM <= 30)

I'm getting the error
 Quote: SQLCODE = -206, ERROR: ROWID IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER

RowID is not recognized in DB2.Pls give the soln if possible....
khamarutheen

Active Member

Joined: 23 Aug 2005
Posts: 680
Location: NJ

 Posted: Tue Dec 27, 2005 11:13 am    Post subject: Select query Hi nikyojin, Rowid is not a system defined column in DB2 it is in oracle only. so i dont think u can get solved by the above. try with ur primary key it will work. let me know if u r still on confusion
nikyojin

New User

Joined: 05 Oct 2005
Posts: 94

 Posted: Tue Dec 27, 2005 2:35 pm    Post subject: Re: selecting records from 20 to 30 in a table Hi khamarutheen, Thanks for the reply...I was just confused cause of the "Row-ID" col. Regards, Nikhil .S.
khamarutheen

Active Member

Joined: 23 Aug 2005
Posts: 680
Location: NJ

 Posted: Tue Dec 27, 2005 2:39 pm    Post subject: BETWEEN Hi nikyjin, Nothing to get confuse yar. b clear v dont have rowid or rowcol in DB2 as like in oracle. so only v use FETCH option to fetch first 10 rows or last 10 rows like that. else use BETWEEN with the help of primary key inorder to fetch the particular range of rows.
mijanurit
Currently Banned

New User

Joined: 26 Aug 2005
Posts: 33
Location: bangalore

 Posted: Sun Jan 01, 2006 10:52 pm    Post subject: Re: selecting records from 20 to 30 in a table hi dandapani and other experts, i am trying to give the sql statement of the above query. if i am wrong plz correct me. let ur table name EMP_TAB contains 100 rows. SELECT * FROM (SELECT * FROM EMP_TAB FETCH FIRST 30 ROWS ONLY) FETCH LAST 10 ROWS ONLY plz tell me whether i am correct or wrong. regards mija
ANIKET

New User

Joined: 28 Apr 2005
Posts: 11
Location: PUNE

 Posted: Tue Jan 03, 2006 5:14 pm    Post subject: Mija I dont think DB2 supports "LAST" verb in this sense. Although it is good suggestion.
nikyojin

New User

Joined: 05 Oct 2005
Posts: 94

 Posted: Wed Jan 04, 2006 11:11 am    Post subject: Re: selecting records from 20 to 30 in a table Great Soln Mija...smart thinking...but DB2 doesn't support "Fetch first n rows only" in the subquery
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm Selecting two copybooks of different ... Vignesh Sid COBOL Programming 8 Tue Sep 05, 2017 7:28 pm PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 21 Sun Aug 27, 2017 10:35 pm

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us