Printable View
Hi,
select unique(a.NMBREVENTID), a.STRNAME
from bb_tradeshow a , bb_trade_category b
where a.nmbreventid = b.nmbreventid and a.nmbreventid=28;
This query returns 5000 rows. I am calling this sql from client application. I wanted to display only 20 rows at a time.
Everytime this query returns 5000 rows.
Is there anyway in SQL i can give condition to return first 20 then second 20 onwards.
Is it procedure is required for that.
Your help is greatly appreciated!.
Thanks
Padsp
But what is the problem if it returns 5000 rows ?
use pl/sql table.load all 5000 to a pl/sql table and give it a condition to return 20 at a time
hi,
whtz ur actula trequirement?
if u want to insert or manipulate with 20 records
then u can use LIMIT function using BULK COLLECT.
revertback
thnx,
Hi,
My exact requirement in this query is, I wanted to display the results to the client application. It's like first 20,next 20 and so on. Now when i execute this query for the first time entire results(5000 rows) being return to client appln. but i am displaying 20 rows out of 5000. When user click on next 20, again i have to execute the same query, discard first 20 i'm displaying 20-40 records.
every time it returns entire result sets. Instead i wanted to return only 20 rows to the user.
I do not know about pl/sql table. i'm searching documents for that.
thanks
This is a standard 'windowing' problem. If you search this forum for WINDOW ROWNUM, you will find many threads on it.
Here is the standard wrapper for turning a statement into a windowed statement:
Code:SELECT
OUTER.C1
FROM
(
SELECT
INNER.C1,
ROWNUM
AS QUERY_ROWNUM
FROM
(
SELECT
C1
FROM
TABLE1
ORDER BY
C1
) INNER
WHERE
ROWNUM < 20
) OUTER
WHERE
OUTER.QUERY_ROWNUM >= 1
HTH,
- Chris
Hi,
Thank you for the query. its working. appreciate your help.
Had this problem before and used a procedure to return the required ammount of rows...you simply pass in the number to start at and the number to end at...
heres the code for it.. hope it helps
create or replace package LSPD_summaries as
type t_v15 is table of varchar2(15) index by binary_integer;
type t_n10 is table of number(10) index by binary_integer;
type t_v100 is table of varchar2(100) index by binary_integer;
type t_v75 is table of varchar(75) index by binary_integer;
type t_v150 is table of varchar2(150) index by binary_integer;
type t_v50 is table of varchar(50) index by binary_integer;
type t_v30 is table of varchar2(30) index by binary_integer;
procedure getSummaries( o_error_num out number,
o_rowCount out number,
i_start in number,
i_finish in number,
o_FILE_REFERENCE OUT T_V15,
o_ID OUT T_N10,
o_COUNTY OUT T_V100,
o_TYPE OUT T_V75,
o_TRANSACTION OUT T_V75,
o_THIRD_PARTY OUT T_V150,
o_LEGAL_CONTACT OUT T_V50,
o_STATUS OUT T_V30
);
end;
/
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
create or replace package body LSPD_summaries as
procedure getSummaries( o_error_num out number,
o_rowCount out number,
i_start in number,
i_finish in number,
o_FILE_REFERENCE OUT T_V15,
o_ID OUT T_N10,
o_COUNTY OUT T_V100,
o_TYPE OUT T_V75,
o_TRANSACTION OUT T_V75,
o_THIRD_PARTY OUT T_V150,
o_LEGAL_CONTACT OUT T_V50,
o_STATUS OUT T_V30
)is
v_fileRef varchar2(15);
v_id NUMBER(10);
v_county varchar2(100);
v_type varchar2(75);
v_transaction varchar2(75);
v_thirdParty varchar2(150);
v_legalContact varchar2(50);
v_status varchar2(30);
x Number :=0;
CURSOR getSummaries is
SELECT LP.FILE_REFERENCE, LP.ID, LC.COUNTY, LTYPE.TYPE, LTRAN.TRANSACTION,
LP.THIRD_PARTY, LCON.LSPD_LEGAL_CONTACT, LS.STATUS
FROM LSPD_LEGAL_PROPERTY LP, LSPD_LEGAL_COUNTY LC, LSPD_LEGAL_TYPE LTYPE,
LSPD_LEGAL_TRANSACTION LTRAN, LSPD_LEGAL_CONTACT LCON, LSPD_LEGAL_STATUS LS
WHERE LP.COUNTY_ID = LC.COUNTY_ID
AND LP.TYPE_ID = LTYPE.TYPE_ID
AND LP.TRANSACTION_ID = LTRAN.TRANSACTION_ID
AND LP.LSPD_LEGAL_CONTACT_ID = LCON.LSPD_LEGAL_CONTACT_ID
AND LP.STATUS_ID = LS.STATUS_ID (+)
AND ((LP.BF_DATE <= TRUNC(SYSDATE))
OR ((LP.STATUS_ID != 4) AND (LP.COMPLETION_DATE BETWEEN SYSDATE AND (TRUNC(SYSDATE) + 7))))
ORDER BY ((TRUNC(LP.COMPLETION_DATE)) - (TRUNC(SYSDATE))) ;
begin
open getSummaries;
FETCH getSummaries INTO v_fileRef,v_id,v_county,v_type,
v_transaction,v_thirdParty,
v_legalContact,v_status;
while (getSummaries%ROWCOUNT <= i_start ) LOOP
FETCH getSummaries INTO v_fileRef,v_id,v_county,v_type,
v_transaction,v_thirdParty,
v_legalContact,v_status;
END LOOP;
while (getSummaries%ROWCOUNT <= i_finish ) LOOP
x := x + 1;
o_FILE_REFERENCE(x) := v_fileRef;
o_ID(x) := v_id;
o_COUNTY(x) := v_county;
o_TYPE(x) := v_type;
o_TRANSACTION(x) := v_transaction;
o_THIRD_PARTY(x) := v_thirdParty;
o_LEGAL_CONTACT(x) := v_legalContact;
o_STATUS(x) := v_status;
FETCH getSummaries INTO v_fileRef,v_id,v_county,v_type,
v_transaction,v_thirdParty,
v_legalContact,v_status;
END LOOP;
while (getSummaries%FOUND) LOOP
FETCH getSummaries INTO v_fileRef,v_id,v_county,v_type,
v_transaction,v_thirdParty,
v_legalContact,v_status;
END LOOP;
o_rowCount := getSummaries%ROWCOUNT;
-- Return Success
o_error_num := 0;
End;
Wow! What a great way to write 200 lines of code when one query will do!
chrisrlong,
can you please explain how the INNER and OUTER works? Where can I find documentation on these?
Also in your example, how would the statement look the 2nd time you would call it to get the next chunk?
thanks
aK
you can try this
SELECT * from ( SELECT Rownum rn, tab1.* FROM tab1 )
WHERE rn between lower_value and Upper_value;
where tab1 is the table name and the lower_value and the upper_value specify the starting row and the end row numbers.
hope this helps you
swaminathan
Too, too funny, Jeff!! :DQuote:
Originally posted by marist89
Wow! What a great way to write 200 lines of code when one query will do!
Pauliemacc, I'm afraid Jeff is right. Why would you want to go through all that PL/SQL when you can do it in SQL? On top of that, the PL/SQL version will be slower. Feel free to time them if you don't believe me. Depending on the exact query, the SQL solution can be *much* faster, but regardless of the query, it will never be slower than the PL/SQL code.
INNER and OUTER are just aliases for the in-line views I created. They only contribute to the readability of the statement, not the functionality. The functionality resides in the structure of the statement and the use of ROWNUM.Quote:
Originally posted by akatsari
can you please explain how the INNER and OUTER works? Where can I find documentation on these?
All you have to do is change the Row numbers you want:Quote:
Originally posted by akatsari
Also in your example, how would the statement look the 2nd time you would call it to get the next chunk?
And finally, we have:Code:SELECT
OUTER.C1
FROM
(
SELECT
INNER.C1,
ROWNUM
AS QUERY_ROWNUM
FROM
(
SELECT
C1
FROM
TABLE1
ORDER BY
C1
) INNER
WHERE
ROWNUM < 40
) OUTER
WHERE
OUTER.QUERY_ROWNUM >= 21
First, I'm not sure why you felt the need to answer a question that has already been answered - especially with almost exactly the same solution. Regardless, I feel the need to comment on this solution because, while it is *almost* exactly the same, it does not support the same functionality that my solution does. Your solution does not support an ORDER BY, which is almost always required in a windowing situation.Quote:
Originally posted by rswamin
SELECT * from ( SELECT Rownum rn, tab1.* FROM tab1 )
WHERE rn between lower_value and Upper_value;
Hope that clears up everything. Again, for more information, search on WINDOW ROWNUM in this forum.
- Chris
[Edited by chrisrlong on 02-11-2002 at 10:17 AM]