Hello. There is a problem with the simplification of the sql query. Here is my request.

SELECT count(*) from ORACLE.Z_DELIVERY where batch like '%' || :pipeYear || '-' || :pipeNumber and ORG_ID = :shopID and VBTYP = 'T' and PSTNG_DATE =( select max(PSTNG_DATE) from Z_DELIVERY where batch like '%' || :pipeYear || '-' || :pipeNumber and ORG_ID = :shopID and VBTYP = 'T' )

But the problem is that like it works twice for a very long time. And since in the external query with the batch field, there is already a comparison like. After all, you can replace it in the internal one using join. If anyone can tell how to do this preferably with an example, I will be very grateful. Thanks in advance.

  • one
    And just group by PSTNG_DATE, sort by descending order and take the first record - not? - Akina
  • You would attach a table structure, indexes, and query plan. Without them, query optimization looks like fortune telling - Viktorov

3 answers 3

Something like this:

 select * from (select zd.*, row_number() over (partition by org_id, vbtyp order by psting_date desc) rn from z_delivery zd) where rn = 1 and batch like '%' || :pipeYear || '-' || :pipeNumber and ORG_ID = :shopID and VBTYP = 'T'

And the index across the PSTNG_DATE field will be very useful (if not already).

  • You have something strange written here. TCA was considered the sum of rows for a date, you obviously have something else - Viktorov
  • Just * replace with count(*) . - Dmitriy
  • The selection conditions are best moved to an internal subquery. but it turns out we need to first enumerate all the entries in the table, and then throw out most of them because like, etc. I failed. in addition, if by org_id there is an index in the internal subquery, it can be used, but in the external one no longer exists - Mike

Let's use the FIRST function, KEEP sentences:

 SELECT count(*) keep(dense_rank first order by PSTNG_DATE desc) from ORACLE.Z_DELIVERY where batch like '%' || :pipeYear || '-' || :pipeNumber and ORG_ID = :shopID and VBTYP = 'T'
  • Mike, thanks, good option. Only a little changed the idea. You just need to sort the query with these conditions without unnecessary functions by date and select the first record. And instead of count (*) just leave *. If you can please give an example. Thanks in advance. - Andrew

You can try this:

 select cnt from ( select PSTNG_DATE, count(1) as cnt from ORACLE.Z_DELIVERY t where t.batch like '%' || :pipeYear || '-' || :pipeNumber and t.ORG_ID = :shopID and t.VBTYP = 'T' group by PSTNG_DATE order by PSTNG_DATE desc ) t where rownum < 2

In the nested subquery we calculate the amount for each of the days, and in the external we will display the last day.

Let me emphasize right away that this query will not necessarily work faster than yours (for example, if you have a partition by date, my query will obviously be slower). To optimize the query, you should see the structure of the table, the indexes on it and the query plan.

  • lDrakonl Your request is faster, but I need a little more now. The idea has changed. You just need without all the extras to fulfill my request and sort by date strings. And choose the first line which will be with the maximum date. That's all. If there is an example I will be grateful. Thanks in advance. - Andrew
  • @ Andrei Is my request not the same? And look carefully at the answer Mike, he just does not suit you? - Viktorov
  • lDrakonl, I slightly redid as I really needed almost your request and came out just a little needed differently. Here is how. select count(1) from ( select batch from ORACLE.Z_DELIVERY t where t.batch like '%' || :pipeYear || '-' || :pipeNumber and t.ORG_ID = :shopID and t.VBTYP = 'T' order by PSTNG_DATE desc ) t where rownum < 2 - Andrew
  • Thanks for the help. - Andrew
  • Your request will simply display the number of rows for your condition that is not tied to dates. - Viktorov