Limitations in an SQL query

Asked

Viewed 66 times

0

I have to make a report that shows the main customers of each seller (the top 10 customers who spent on each seller)

I am using the Oracle database to make this query.

Each seller has more than 40 customers, I need to limit 10 customers to all sellers.

The select I’m wearing:

 SELECT * FROM (   SELECT FILIAL, vendedor, CODV, cliente,SUM(valor), PERIODO fROM (SELECT ITPED.CODFIL FILIAL,
            ITPED.CODVENDR CODV,
            MAX(VEND.NOME) vendedor,
            cliente.nomcli cliente,
            SUM(ITPED.VLTOTITEM) VALOR,
            TO_CHAR(ITPED.DTPEDIDO, 'mm/yyyy') PERIODO
       FROM MOV_ITPED  ITPED,
            CAD_ITPROD ITPROD,
            VEN_VEND   VEND,
            cad_cliente cliente
      WHERE ITPROD.CODITPROD = ITPED.CODITPROD AND
            ITPED.CODVENDR = VEND.CODVENDR AND
            ITPED.CODFIL = VEND.CODFIL AND
            itped.codcli = cliente.codcli AND
            ITPED.DTPEDIDO BETWEEN '01/03/2019' AND '18/04/2019' AND
            ITPED.CODFIL = 3 AND ITPED.STATUS <> 9
      GROUP BY ITPED.CODFIL,
               ITPED.CODVENDR,
               ITPED.DTPEDIDO,
               cliente.nomcli

     UNION ALL

     SELECT ITSAIDA.CODFIL FILIAL,
            ITSAIDA.CODVENDR CODV,
            MAX(VEND.NOME) vendedor,
            cliente.nomcli cliente,
            SUM(ITSAIDA.VLTOTITEM) VALOR,
            TO_CHAR(ITSAIDA.DTNOTA, 'mm/yyyy') PERIODO
       FROM MOV_ITSAIDA ITSAIDA,
            CAD_ITPROD  ITPROD,
            VEN_VEND    VEND,
            cad_cliente cliente
      WHERE ITPROD.CODITPROD = ITSAIDA.CODITPROD AND
            ITSAIDA.CODVENDR = VEND.CODVENDR AND
            ITSAIDA.CODFIL = VEND.CODFIL AND
            ITSAIDA.codcli = cliente.codcli AND
            ITSAIDA.DTNOTA BETWEEN '01/03/2019' AND '18/04/2019' AND
            ITSAIDA.CODFIL = 3 AND ITSAIDA.STATUS <> 9
      GROUP BY ITSAIDA.CODFIL,
               ITSAIDA.CODVENDR,
               cliente.nomcli,
               ITSAIDA.DTNOTA)  GROUP BY FILIAL, vendedor, CODV, cliente, PERIODO ORDER BY 3 ASC, 5 DESC ) WHERE ROWNUM <= 10

I was wondering if it would be possible to make a Loop by passing the sellers' codes, because I was able to limit the amount of line the select returns.

  • What this query returns to you and what you want it to return?

  • It returns the seller’s branch, the seller’s name and all customers who made purchases with it, and the month the purchase was made. However have seller who made purchases for more than 40, would need to pick up type 10 customers who spent more for each seller.

  • The ROWNUM <= 10 clause does not comply?

  • Actually no, I used it to try to limit the amount of customer but it limits the entire select, in case only the first seller and its customers will appear, but the second seller does not appear.

  • https://oracle-base.com/articles/miscanalytic-functions http://www.oracletutorial.com/oracle-analytic-functions/oracle-rank/ use analytic functions, examples of this top n for any break , RANK , DENSE_RANK etc

No answers

Browser other questions tagged

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