Add 2 selects to one, showing the fields

Question

Add 2 selects to one, showing the fields

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

The 1st select shows the following fields:

CONCAT - FILIAL - COD. PRODUTO - DESCRICAO - QNTD. VENDAS PUB.
  11   -   1    -       1      - PRODUTO 1 -        10
  12   -   1    -       2      - PRODUTO 2 -        20
  13   -   1    -       3      - PRODUTO 3 -        30

The 2nd select shows the following fields:

CONCAT - FILIAL - COD. PRODUTO - DESCRICAO - QNTD. VENDAS PRIV.
  11   -   1    -       1      - PRODUTO 1 -        11
  12   -   1    -       2      - PRODUTO 2 -        22
  13   -   1    -       3      - PRODUTO 3 -        33
  19   -   1    -       9      - PRODUTO 9 -        99

I need to put together these 2 selects where you can show the result as follows:

CONCAT - FILIAL - COD. PRODUTO - DESCRICAO - QNTD. VENDAS PUB. - QNTD. VENDAS PRIV.
  11   -   1    -       1      - PRODUTO 1 -        10          -        11
  12   -   1    -       2      - PRODUTO 2 -        20          -        22
  13   -   1    -       3      - PRODUTO 3 -        30          -        33
  19   -   1    -       9      - PRODUTO 9 -         0          -        99

Using union does not answer me because the sums of the QNTD field. PRIVATE SALES. would be in the same field as the publ.

SELECT
  CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT,
  PCPEDC.CODFILIAL AS FILIAL,
  PCPEDI.CODPROD AS "COD. PRODUTO",
  PCPRODUT.DESCRICAO AS DESCRICAO,
  Sum(PCPEDI.QT) AS "QNTD. VENDAS PUB."
FROM
  PCPEDI,
  PCCLIENT,
  PCPEDC,
  PCATIVI,
  PCPRODUT
WHERE
  PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 IN (60, 52, 51, 43, 44, 49)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY
  PCPEDI.CODPROD,
  PCPEDC.CODFILIAL
ORDER BY
  "COD. PRODUTO"


SELECT
  CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT,
  PCPEDC.CODFILIAL AS FILIAL,
  PCPEDI.CODPROD AS "COD. PRODUTO",
  PCPRODUT.DESCRICAO AS DESCRICAO,
  Sum(PCPEDI.QT) AS "QNTD. VENDAS PRIV."
FROM
  PCPEDI,
  PCCLIENT,
  PCPEDC,
  PCATIVI,
  PCPRODUT
WHERE
  PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 NOT IN (60, 52, 51, 43, 44, 49, 62, 64, 2, 99, 1)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY
  PCPEDI.CODPROD,
  PCPEDC.CODFILIAL
ORDER BY
  "COD. PRODUTO"

Developer solution:

SELECT VENDAS_PUBLICAS.CONCAT,
       --demais colunas
       VENDAS_PUBLICAS.QNTD_VENDAS_PUB, 
       VENDAS_PRIVADAS.QNTD_VENDAS_PRIV 
FROM 
(SELECT
  CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT,
  PCPEDC.CODFILIAL AS FILIAL,
  PCPEDI.CODPROD AS "COD. PRODUTO",
  PCPRODUT.DESCRICAO AS DESCRICAO,
  Sum(PCPEDI.QT) AS "QNTD_VENDAS_PUB"
FROM PCPEDI, PCCLIENT, PCPEDC, PCATIVI, PCPRODUT
WHERE PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 IN (60, 52, 51, 43, 44, 49)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY PCPEDI.CODPROD, PCPEDC.CODFILIAL 
ORDER BY "COD. PRODUTO") as VENDAS_PUBLICAS,
(SELECT CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT, 
       PCPEDC.CODFILIAL AS FILIAL,
       PCPEDI.CODPROD AS "COD. PRODUTO", 
       PCPRODUT.DESCRICAO AS DESCRICAO, 
       Sum(PCPEDI.QT) AS "QNTD_VENDAS_PRIV"
FROM PCPEDI, PCCLIENT, PCPEDC, PCATIVI, PCPRODUT 
WHERE PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 NOT IN (60, 52, 51, 43, 44, 49, 62, 64, 2, 99, 1)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY PCPEDI.CODPROD, PCPEDC.CODFILIAL
ORDER BY "COD. PRODUTO") as VENDAS_PRIVADAS
WHERE VENDAS_PUBLICAS.CONCAT = VENDAS_PRIVADAS.CONCAT

sql oracle

asked by anonymous 01.05.2016 / 01:27

2 answers

How do I check if a password is correct in an ATM project? How to resolve pull request with deleted repository?

score 0 · Answer 1

0

Make an outer join using the selects as virtual tables

select *
from (select ... concat ... ) s1,
     (select ... concat ... ) s2
where s1.concat (+) = s2.concat

02.05.2016 / 15:23

score 0 · Answer 2

Here's the change to your query with the Motta solution:

SELECT VENDAS_PUBLICAS.CONCAT,
       --demais colunas
       VENDAS_PUBLICAS.[QNTD. VENDAS PUB.], 
       VENDAS_PRIVADAS.[QNTD. VENDAS PRIV.] 
FROM 
(SELECT
  CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT,
  PCPEDC.CODFILIAL AS FILIAL,
  PCPEDI.CODPROD AS "COD. PRODUTO",
  PCPRODUT.DESCRICAO AS DESCRICAO,
  Sum(PCPEDI.QT) AS "QNTD. VENDAS PUB."
FROM PCPEDI, PCCLIENT, PCPEDC, PCATIVI, PCPRODUT
WHERE PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 IN (60, 52, 51, 43, 44, 49)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY PCPEDI.CODPROD, PCPEDC.CODFILIAL 
ORDER BY "COD. PRODUTO") as VENDAS_PUBLICAS,
(SELECT CONCAT(PCPEDC.CODFILIAL, PCPEDI.CODPROD) AS CONCAT, 
       PCPEDC.CODFILIAL AS FILIAL,
       PCPEDI.CODPROD AS "COD. PRODUTO", 
       PCPRODUT.DESCRICAO AS DESCRICAO, 
       Sum(PCPEDI.QT) AS "QNTD. VENDAS PRIV."
FROM PCPEDI, PCCLIENT, PCPEDC, PCATIVI, PCPRODUT 
WHERE PCPEDI.CODPROD = PCPRODUT.CODPROD
  AND PCPEDI.CODCLI = PCCLIENT.CODCLI
  AND PCPEDI.NUMPED = PCPEDC.NUMPED
  AND PCCLIENT.CODATV1 = PCATIVI.CODATIV
  AND PCPEDI.POSICAO = 'F'
  AND PCCLIENT.CODATV1 NOT IN (60, 52, 51, 43, 44, 49, 62, 64, 2, 99, 1)
  AND PCPEDC.CODFILIAL IN (1)
  AND PCPEDC.CONDVENDA = 1
GROUP BY PCPEDI.CODPROD, PCPEDC.CODFILIAL
ORDER BY "COD. PRODUTO") as VENDAS_PRIVADAS
WHERE VENDAS_PUBLICAS.CONCAT = VENDAS_PRIVADAS.CONCAT

In my understanding, the codfilial and codprod columns identify each row, so this solution will work.

The solution is to transform your queries into tables to be able to join.