Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

selecting records from 20 to 30 in a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dandapani

New User


Joined: 13 Dec 2005
Posts: 7
Location: banglore

PostPosted: Wed Dec 14, 2005 7:44 pm    Post subject: selecting records from 20 to 30 in a table
Reply with quote

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
Back to top
View user's profile Send private message

iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

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

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.

Check the answers,


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.
Back to top
View user's profile Send private message
dandapani

New User


Joined: 13 Dec 2005
Posts: 7
Location: banglore

PostPosted: Thu Dec 15, 2005 2:53 pm    Post subject: Thanks for posting your comments
Reply with quote

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.
Back to top
View user's profile Send private message
iknow

Active User


Joined: 22 Aug 2005
Posts: 413
Location: Colarado, US

PostPosted: Thu Dec 15, 2005 3:02 pm    Post subject: Re: selecting records from 20 to 30 in a table
Reply with quote

Hi dandapani,

Try with ROWID instead of ROWNUM.

I will try to give the solution to your query.
Back to top
View user's profile Send private message
anilbatta

New User


Joined: 13 Nov 2005
Posts: 22

PostPosted: Fri Dec 16, 2005 3:39 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

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

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....
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Dec 27, 2005 11:13 am    Post subject: Select query
Reply with quote

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
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Tue Dec 27, 2005 2:35 pm    Post subject: Re: selecting records from 20 to 30 in a table
Reply with quote

Hi khamarutheen,

Thanks for the reply...I was just confused cause of the "Row-ID" col.

Regards,
Nikhil .S.
Back to top
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Dec 27, 2005 2:39 pm    Post subject: BETWEEN
Reply with quote

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.
Back to top
View user's profile Send private message
mijanurit
Currently Banned

New User


Joined: 26 Aug 2005
Posts: 33
Location: bangalore

PostPosted: Sun Jan 01, 2006 10:52 pm    Post subject: Re: selecting records from 20 to 30 in a table
Reply with quote

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
Back to top
View user's profile Send private message
ANIKET

New User


Joined: 28 Apr 2005
Posts: 11
Location: PUNE

PostPosted: Tue Jan 03, 2006 5:14 pm    Post subject:
Reply with quote

Mija
I dont think DB2 supports "LAST" verb in this sense. Although it is good suggestion.
Back to top
View user's profile Send private message
nikyojin

New User


Joined: 05 Oct 2005
Posts: 94

PostPosted: Wed Jan 04, 2006 11:11 am    Post subject: Re: selecting records from 20 to 30 in a table
Reply with quote

Great Soln Mija...smart thinking...but DB2 doesn't support "Fetch first n rows only" in the subquery
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us