Re: SQL statement to return a block of rows

From: Michael McMullen <ganstadba_at_hotmail.com>
Date: Fri, 20 Aug 2004 15:20:26 -0400
Message-ID: <BAY9-DAV3RSGfM7QugG000089e8@hotmail.com>

Do you mean something like this ie. pagination? You want to order the results and bring back the 10th to 20th record? this is from a stored proc but do a search on asktom.oracle.com on pagination

select * from (select to_char(rownum) line_no, a.* from (

     SELECT  to_char(date_iss,'yyyy/mm/dd') order_date,key_ord order_no,
     decode(product, 'AV','Advantage','CE','Centrex','CO','Coin','EK','Push

Button','OT','Other','PB','PBX','RE','Residential','SB','Single Line Business' ,product) product,

     billname customer,crdddst,
     s_o_stus status,
     to_char(date_com,'yyyy/mm/dd')

completed_date,to_char(date_due,'yyyy/mm/dd') due_date,'-' address, ' ' city,

     ' ' province,' ' postal_code,
     decode(ord_type, 'C','Change','F','Move from an old

Address','I','Inward','O','Outward','P','Change Pilot Telephone Number or change class of service Residence to Business','R','Record','T','Move to a new address','X','Change billing record', ord_type) order_type,

     btn,'-' install_date
     FROM    me.meomy a
     WHERE
     billname LIKE (sCustomer_IN||'%')
     AND DECODE(s_o_stus,'Cancelled','Completed',s_o_stus) = (
DECODE(sStatus_IN,'Pending','Active','Completed'))
     ORDER BY date_iss desc) a
     where rownum < trunc(to_number(sLineNo_IN)/100)*100+101)
      where line_no > trunc(to_number(sLineNo_IN)/100)*100;

Mike

Original Message ----- From: "DENNIS WILLIAMS" <DWILLIAMS_at_LIFETOUCH.COM> To: <oracle-l_at_freelists.org> Sent: Friday, August 20, 2004 2:57 PM Subject: SQL statement to return a block of rows

> List - In a SQL statement with an ORDER clause, can I provide a starting
and
> end point (or offset) based on rows returned? Oracle9i
>
> We are converting a small application from SQL Server to Oracle (good!).
In
> SQL Server they do everything with stored procedures, but in Oracle we
plan
> to just use SQL statements. In SQL Server one stored procedure retrieves a
> screenfull of rows and returns them, so the cursor is managed on the
> database side. I said that for Oracle we would probably want to do this
> similarly with just SQL statements. This works except for the screenful
> business. I said we could wrap the query in a PL/SQL shell and use a
cursor,
> but the team is thinking there must be a way. If someone has a PL/SQL
> template, please send it to me. One of them uses MySql and says this
feature
> is available there.
>
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
>
> "We all want progress, but if you're on the wrong road, progress means
doing
> an about-turn and walking back to the right road; in that case, the man
who
> turns back soonest is the most progressive."
> -- C.S. Lewis
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Aug 20 2004 - 18:04:17 CDT