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
Difficulty Left Join Oracle 10g
- jorginho19
- Rank: Estagiário Pleno
- Posts: 5
- Joined: Tue, 05 May 2009 11:03 am
- Location: Uberaba
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?
Att
- dr_gori
- 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
Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered
It's inverted!
If you have no request, then the (+) has to be in it.
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
- jorginho19
- Rank: Estagiário Pleno
- Posts: 5
- Joined: Tue, 05 May 2009 11:03 am
- Location: Uberaba
I put it like this and it did not work!
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
- dr_gori
- 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
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:
in all fields of P.
is that you are filtering the P as well.
So you have to put this:
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
- jorginho19
- Rank: Estagiário Pleno
- Posts: 5
- Joined: Tue, 05 May 2009 11:03 am
- Location: Uberaba
It worked beauty, very thank you!
Personnel,
only as information, I took a test in two of my tables and the result was faster:
only as information, I took a test in two of my tables and the result was faster:
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
- jorginho19
- Rank: Estagiário Pleno
- Posts: 5
- Joined: Tue, 05 May 2009 11:03 am
- Location: Uberaba
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 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
- jorginho19
- Rank: Estagiário Pleno
- Posts: 5
- Joined: Tue, 05 May 2009 11:03 am
- Location: Uberaba
Only the level of curiosity is the answer!
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
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 17 guests