Paging

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 26 Oct 2004 12:03 pm

Dear, we have a screen with a little over a thousand records and when this is selected, it brings all records to the screen, leaving the immense.

I want to page her to every 100 records she go to another page.

Does anyone know how?

I hope news
edu_oracle
Location: Salvador

Atenciosamente,

Eduardo Santana

"Viva o dia de hoje como se fosse amanhã, por isso, não perca as oportunidades...."

Poston Tue, 26 Oct 2004 1:26 pm

Well, there's a way that I don't know if it's the best ...
See:
Code: Select all
select b.campo
from
(
select rownum seq, a.*
from
  (select  'numero '||rownum campo      --> este é o SQL que você pode mduar.
  from all_tables                                 
  ) a
order by a.campo
) b
where b.seq>=  ( 3 * 10 ) - 10
  and b.seq<     3 * 10
/
this example will get the page 3, breaking of 10:0 pm 10.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 23 Nov 2004 3:45 pm

Thomas Kyte gave the following tip on paging: http://asktom.oracle.com/pls/ask/f?p=49 ... 7412348064
Code: Select all
select *
  from ( select a.*, rownum rnum
           from ( SEU_SQL_VAI_AQUI -- com order by ) a
          where rownum <= MAX_ROWS )
where rnum >= MIN_ROWS
/
Example:
Code: Select all
select *
  from ( select a.*, rownum rnum
           from
  (select  'numero '||rownum campo
  from all_tables
  ) a
where rownum <= 30 )
where rnum >= 10
tfg
Location: Novo Hamburgo - RS

Poston Tue, 10 Aug 2010 2:19 pm

I have a pagination, but is within a cursor and does not work at all with the tip above.
The script of the procedure with error and the package referenced by her below.
Code: Select all
create or replace
PROCEDURE SUPPSEARCH (
    v_cursor IN OUT PKG_PORTAL.RCT1 ,
    v_dateNow IN VARCHAR2,
   v_country IN VARCHAR2,
   v_city IN VARCHAR2,
   v_praticeField IN NUMBER,
   v_title IN VARCHAR2,
   v_currentPage IN INTEGER ,
   v_pageSize IN INTEGER ,
   v_status IN VARCHAR2
)
AS
BEGIN

OPEN v_cursor FOR

SELECT *
  FROM ( SELECT topn.*, ROWNUM rnum FROM (
SELECT
   S.SUPPID,S.PRAREAID,S.SUPPDATENOW,S.SUPPSTATUS,suppDateNow,P.counNamePT AS suppCountry,PR.prareaTitle
FROM
  Supplier S
inner join
  country p
    on p.counId = s.counId
inner join
  pracarea pr
    on s.prareaId = pr.prareaId
where
  TO_CHAR( s.suppDateNow, 'DD/MM/YYYY' ) = NVL( v_dateNow, TO_CHAR( s.suppDateNow, 'DD/MM/YYYY' ) )
and
  S.counId = NVL( v_country, S.counId )
and
  S.suppCity LIKE '%' || v_city || '%'
and
  S.prareaId = NVL( v_praticeField, S.prareaId )
and
  S.suppStatus = NVL(v_status,s.suppStatus)

order by S.SUPPID;
) topn where ROWNUM <= ( ( ( v_currentPage + 1 ) * v_pageSize )  ) ) where rnum  >= ( v_currentPage * v_pageSize );
END;
Now the Package code:
Code: Select all
create or replace PACKAGE PKG_PORTAL
AS
   TYPE RCT1 IS REF CURSOR ;
   TRANCOUNT INTEGER := 0 ;
   IDENTITY INTEGER ;
END ;
can someone help?
caracamaneh
Location: rio de janeiro - RJ

Poston Tue, 10 Aug 2010 3:06 pm

Which the error that this showing up??


Note: whenever you post code use the tag code dae is easier for everyone to read e.g.
Code: Select all
  teste
SergioLBJr
Location: Parobé - RS

Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Poston Tue, 10 Aug 2010 5:26 pm

In a mailing list a user made this same question a few days ago, follows the response by Chiappa:
the basic technique is something like: select * from (select/* + FIRST_ROWS (n) */a.*, ROWNUM rnum from (here enter your SELECT with order by) the where ROWNUM < =: num_da_ultima_linha) where rnum = >: num_da_primeira_linha;

In http://asktom.oracle.com/pls/apex/f?p=100:11:0:::P: 11_QUESTION_ID: 1137577300346084930 # 2666796700346816567 if discusses some points of performance, but for only 350k lines in a production hardware you should hardly have qualquer issue ...

[s] Chiappa
burga
Location: SP

Poston Wed, 11 Aug 2010 10:48 am

The mistake which features is as follows: Error (22.1): PL/SQL: SQL Statement ignored Error (23.22): PL/SQL: ORA-00918: column ambiguously defined
caracamaneh
Location: rio de janeiro - RJ

Poston Wed, 11 Aug 2010 10:52 am

The error is on line
Code: Select all
SELECT *
  FROM ( SELECT topn.*, ROWNUM rnum FROM (
SELECT
Error (18.1): PL/SQL: SQL Statement ignored Error (19.22): PL/SQL: ORA-00918: column ambiguously defined
caracamaneh
Location: rio de janeiro - RJ

Poston Wed, 11 Aug 2010 1:29 pm

This error means that oracle doesn't know which table it should get the value.

You need to define in which table you want to fetch the rownum.

I believe this should solve:
Code: Select all
SELECT *
  FROM ( SELECT topn.*, topn.ROWNUM rnum FROM (
SELECT
Recalling that in this example I seek the rownum of topn
SergioLBJr
Location: Parobé - RS

Sérgio Luiz Bonemberger Junior
Programador Junior
Parobé RS

[]s

Poston Wed, 11 Aug 2010 3:50 pm

Has the entire query how to post?

Two columns of different tables with the same name are on some innermost query, causand error.
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Wed, 11 Aug 2010 6:03 pm

Rogenaro thank you very much.
The innermost query had the same column twice in the query.
I had deleted this when I posted the code because they are muuuuuitas columns.
so it was not in the code.
But your post made me think and check.
caracamaneh
Location: rio de janeiro - RJ


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 2 guests