Help needed in SQL!

[padsp]

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

[svk]

But what is the problem if it returns 5000 rows ?

[simflex]

use pl/sql table.load all 5000 to a pl/sql table and give it a condition to return 20 at a time

[balajiyes]

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,

[padsp]

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

[chrisrlong]

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:

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

[padsp]

Hi,

Thank you for the query. its working. appreciate your help.

[pauliemacc]

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;

[marist89]

Wow! What a great way to write 200 lines of code when one query will do!

[akatsari]

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