With limit Select output on Oracle

Asked

Viewed 715 times

1

I’m making a bank select and it’s returning me 15 lines. I used ROWNUM to pick up the first 8 lines, only I want to do another select and pick up from line 9 to 15.

SELECT * FROM (SELECT DISTINCT o.STORAGE_TYPE, COUNT(o.QUANTIDADE) as 
qtd_materiais   
FROM mopsao.vw_monitoramento_do@MOPSAO m INNER JOIN VIEW_ORDENS_A_COLETAR o 
ON 
m.DELIVERY = o.DELIVERY AND m.cod_mat_c = o.MATERIAL WHERE 
m.status_andamento = 'Pickado' 
GROUP BY o.STORAGE_TYPE ORDER BY qtd_materiais DESC) WHERE ROWNUM <= 8

This first select as I described, take the first 8 lines.

sql database oracle select

  • Rownum server to limit records even, apparently you want to do a pagination deal for your application, correct? In this case you need to treat in your application the fetch of the database records, limiting 8 in 8 records. You can look for something specific in the library that you use to make the connection to the oracle, you will probably get a more complete explanation of how to make the pagination

2 answers

2

Good morning friend, for such demand, it is necessary to use FETCH passing as parameter the amount of lines you want in the range.

Example: Offset -> Number of lines Fetch -> Offset + Line range

DECLARE @Off AS INT
SET @Off = @OFFSET
SELECT Coluna1, Coluna2
  FROM Tabela
  OFFSET (@Off) ROWS FETCH NEXT 5 ROWS ONLY;

  • Replaces the Offset that are uppercase for the number of lines I want to display, but it didn’t work

  • Guilherme, as I already commented there in the question, tries to take a look at the tool you use to connect your application with oracle, 99% chance she has resources to control fetch/paging, in case the tool itself does these steps that was answered by @Fabiano-s.

1

That should solve your case: SELECT Lin. * FROM (SELECT DISTINCT o.STORAGE_TYPE, COUNT(o. QUANTITY) as qtd_materials, rownum as lines
FROM mopsao.vw_monitoring@MOPSAO m INNER JOIN V IEW_ORDENS_A_COLETAR o ON m.DELIVERY = o.DELIVERY AND m.cod_mat_c = o.MATERIAL WHERE m.status_tempo = 'Pickado' GROUP BY o.STORAGE_TYPE ORDER BY qtd_materials DESC) Lin Where Lin.row <= 8

  • It didn’t work, still gives error

  • what’s wrong? I ran some tests here and it worked,

  • This error appeared ORA-00905: Missing keyword

  • I want to catch from line 8 to line 16. That’s how you did it, is displaying the error up

Browser other questions tagged sql database oracle select

You are not signed in. Login or sign up in order to post.