-
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 ?
svk
-
help needed in sql
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,
Cheers!
OraKid.
-
exact requirement!
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!
Jeff Hunter
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|