Difficulty Left Join Oracle 10g

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
User avatar
jorginho19
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 05 May 2009 11:03 am
Location: Uberaba
ATT
Jorge Gonçalves Filho
Tecnico em Informática
jorge@globalcosmeticos.com.br

Good morning everyone, I have the following problem in Oracle 10g. When you run the command below, as a result you only have the items that you have listed in the two tables and need you to present all representatives even if it has no request. The way I'm doing this right?

Select all

SELECT R.NMREPRES AS NOME  , NVL(COUNT(P.NRPEDIDO), 0) AS TOTAL 
FROM REPRESENTANTE R, PEDIDO P 
WHERE R.CDREPRES (+) =  P.CDREPRES AND 
      P.STPEDIDO     = 'P' 
GROUP BY R.NMREPRES
Att
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

It's inverted!

Select all

SELECT R.NMREPRES AS NOME  , NVL(COUNT(P.NRPEDIDO), 0) AS TOTAL 
FROM REPRESENTANTE R, PEDIDO P 
WHERE R.CDREPRES =  P.CDREPRES(+)   
   AND P.STPEDIDO     = 'P' 
GROUP BY R.NMREPRES
If you have no request, then the (+) has to be in it.
User avatar
jorginho19
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 05 May 2009 11:03 am
Location: Uberaba
ATT
Jorge Gonçalves Filho
Tecnico em Informática
jorge@globalcosmeticos.com.br

I put it like this and it did not work!

Select all

SELECT R.NMREPRES AS NOME  , NVL(COUNT(P.NRPEDIDO), 0) AS TOTAL 
 
FROM REPRESENTANTE R, PEDIDO P 
 
WHERE R.CDREPRES  =  P.CDREPRES (+) AND 
      P.STPEDIDO     = 'P' 
 
GROUP BY R.NMREPRES
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

You're right.
is that you are filtering the P as well.
So you have to put this:

Select all

SELECT R.NMREPRES AS NOME  , NVL(COUNT(P.NRPEDIDO), 0) AS TOTAL 
FROM REPRESENTANTE R, PEDIDO P 
WHERE R.CDREPRES =  P.CDREPRES(+)  
   AND P.STPEDIDO(+)     = 'P' 
GROUP BY R.NMREPRES
in all fields of P.
User avatar
jorginho19
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 05 May 2009 11:03 am
Location: Uberaba
ATT
Jorge Gonçalves Filho
Tecnico em Informática
jorge@globalcosmeticos.com.br

It worked beauty, very thank you!
Cilso
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Tue, 28 Apr 2009 1:35 pm
Location: Araraquara-SP

Personnel,

only as information, I took a test in two of my tables and the result was faster:

Select all

  SELECT R.NMREPRES AS NOME  , NVL(COUNT(P.NRPEDIDO), 0) AS TOTAL  
FROM REPRESENTANTE R LEFT JOIN PEDIDO P ON (R.CDREPRES =  P.CDREPRES AND P.STPEDIDO = 'P' ) 
GROUP BY R.NMREPRES
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

It was probably faster because in the second attempt the blocks were cached.
User avatar
jorginho19
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 05 May 2009 11:03 am
Location: Uberaba
ATT
Jorge Gonçalves Filho
Tecnico em Informática
jorge@globalcosmeticos.com.br

If someone can help me the first question was one of the doubts to make the SQL below to work and the same is making me break your head if the representative has ordered beleza plus those who have not asked yet do not appear! Can someone help me?

Select all

SELECT R.CDSUPERV, R.CDREPRES, R.NMREPRES,  
       COUNT(DISTINCT(PR.CDPRODUT)) AS MIX_ATENDIDO, 
       (SELECT COUNT(DISTINCT(P.CDPRODUT) ) 
       FROM PRODUTO P, FORNECEDOR F, LIVPRECO L 
       WHERE F.CDFORNEC = 57708       AND 
            F.CDFORNEC = P.CDFABRIC   AND 
            P.CDPRODUT = L.CDPRODUT   AND 
            L.NRSEMANA >= (200902)    AND 
            L.NRSEMANA <= (200915) 
       ) AS MIX_TOTAL, 
       COUNT(DISTINCT(P.CDCLIENT)) AS CLIENTES_ATENDIDOS, 
       (SELECT COUNT(C.CDCLIENT) 
        FROM CLIENTE C, ZONEMPRESA Z 
        WHERE R.CDREPRES = Z.CDREPRES AND 
              C.STLIBERA = 'S'        AND 
              C.CDZONA  = Z.CDZONA     
       ) AS CLIENTES_TOTAL, 
       ROUND(SUM(I.QTAPROVA * I.VRUNITAR),2)AS VENDAS_TOTAL       
         
FROM   REPRESENTANTE R, PEDIDO P, ITEPEDIDO I, FORNECEDOR F, PRODUTO PR 
         
WHERE  R.CDSUPERV  NOT IN (191,100)                           AND 
       F.CDFORNEC      =  57708                               AND 
       F.CDFORNEC      =  PR.CDFABRIC                         AND 
       R.CDREPRES      =  P.CDREPRES  (+)                     AND 
       P.STPEDIDO  (+) =  'F'                                 AND 
       P.NRPEDIDO      =  I.NRPEDIDO                          AND 
       P.CDEMPRES      =  I.CDEMPRES                          AND 
       P.DTPEDIDO      >= TO_DATE('14/01/2009', 'DD/MM/YYYY') AND 
       P.DTPEDIDO      <= TO_DATE('18/04/2009', 'DD/MM/YYYY') AND 
       PR.CDPRODUT     =  I.CDPRODUT                                 
GROUP BY R.CDSUPERV, R.CDREPRES, R.NMREPRES 
 
ORDER BY R.CDSUPERV, R.NMREPRES
User avatar
jorginho19
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 5
Joined: Tue, 05 May 2009 11:03 am
Location: Uberaba
ATT
Jorge Gonçalves Filho
Tecnico em Informática
jorge@globalcosmeticos.com.br

Only the level of curiosity is the answer!

Select all

SELECT R.CDSUPERV, R.CDREPRES, R.NMREPRES,  
       COUNT(DISTINCT(PR.CDPRODUT)) AS MIX_ATENDIDO, 
       (SELECT COUNT(DISTINCT(P.CDPRODUT) ) 
       FROM PRODUTO P, FORNECEDOR F, LIVPRECO L 
       WHERE F.CDFORNEC = 57708       AND 
            F.CDFORNEC = P.CDFABRIC   AND 
            P.CDPRODUT = L.CDPRODUT   AND 
            L.NRSEMANA >= (200902)    AND 
            L.NRSEMANA <= (200915) 
       ) AS MIX_TOTAL, 
       COUNT(DISTINCT(P.CDCLIENT)) AS CLIENTES_ATENDIDOS, 
       (SELECT NVL(COUNT(C.CDCLIENT),0) 
        FROM CLIENTE C, ZONEMPRESA Z 
        WHERE R.CDREPRES = Z.CDREPRES AND 
              C.STLIBERA = 'S'        AND 
              C.CDZONA  = Z.CDZONA     
       ) AS CLIENTES_TOTAL, 
       ROUND(NVL(SUM(I.QTAPROVA * I.VRUNITAR),0),2) AS VENDAS_TOTAL       
         
FROM   REPRESENTANTE R, PEDIDO P, ITEPEDIDO I, FORNECEDOR F, PRODUTO PR 
         
WHERE  R.CDSUPERV  NOT IN (191,100)                           AND 
       R.STREPRES       =  'A'                                AND 
       R.CDEQUREP       =  1                                  AND 
       R.CDREPRES       =  P.CDREPRES (+)                     AND 
       P.STPEDIDO  (+)  =  'F'                                AND 
       P.NRPEDIDO       =  I.NRPEDIDO (+)                     AND 
       P.CDEMPRES       =  I.CDEMPRES (+)                     AND 
       P.DTPEDIDO  (+) >= TO_DATE('14/01/2009', 'DD/MM/YYYY') AND 
       P.DTPEDIDO  (+) <= TO_DATE('18/04/2009', 'DD/MM/YYYY') AND 
       PR.CDPRODUT (+)  =  I.CDPRODUT                         AND 
       F.CDFORNEC  (+)  =  57708                              AND 
       F.CDFORNEC  (+)  =  PR.CDFABRIC                       
       
GROUP BY R.CDSUPERV, R.CDREPRES, R.NMREPRES 
 
ORDER BY R.CDSUPERV, R.NMREPRES
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 17 guests