Help needed in SQL!
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Help needed in SQL!

  1. #1
    Join Date
    May 2001
    Posts
    11
    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

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    But what is the problem if it returns 5000 rows ?
    svk

  3. #3
    Join Date
    Jan 2002
    Posts
    33

    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

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.

  5. #5
    Join Date
    May 2001
    Posts
    11

    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

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  7. #7
    Join Date
    May 2001
    Posts
    11
    Hi,

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

  8. #8
    Join Date
    Jan 2002
    Posts
    2

    Lightbulb

    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;

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Wow! What a great way to write 200 lines of code when one query will do!
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Feb 2002
    Posts
    1
    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
  •  


Click Here to Expand Forum to Full Width