Merge 2 selects into one, showing fields

Asked

Viewed 2,379 times

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

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 join these 2 selects where I can display 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 field QNTD. PRIV SALES. would be in the same field of 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 Pre 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

  • What do you really want with it? Have you tried using the union or the union all?

  • I already tried yes. Only the 2 sums are in the same column. What I need is 2 columns of the sums

  • Your question is a little confused tries to improve she will certainly appear many people to help.

  • Marconi, I edited the description with examples. See if you can help me or someone else.

  • Can someone help me?

  • If you create the QTDE SALES PUB and QTOF PRIV SALES fields for the two selects can’t help? A column will be empty in one of the two select’s

  • Um, got it. I’ll try here and return.

  • @Adalberto worked out well?

Show 3 more comments

2 answers

1

Make an Outer Join using selects as virtual tables

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

0

Follow the change of 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, codfilial and codprod columns identify each row, so this solution will work.

The solution is to turn your queries into tables in order to make a Join.

  • ORA-01747 error: invalid user.table.column, table.column, or column Specification Cause: A column name was specified improperly in the Current SQL statement. Action: Check the statement’s syntax, especially References to column Names, and Retry the statement.

  • You are not recognizing this column: VENDAS_PUBLICAS. [QNTD. PUB SALES. ] I forgot to mention above that the bank is oracle.

  • Replace the spaces with "_", QNTD. PUB SALES. for QNTD_VENDAS_PUB and remove the points and "[", "]". Would look like this :VENDAS_PUBLICAS.QNTD_VENDAS_PUB

  • Another error now: ORA-00933 SQL command not properly ended Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to Insert in a Certain order.

  • Action: Correct the syntax by removing the inappropriate clauses. It may be possible to Duplicate the Removed clause with Another SQL statement. For example, to order the Rows of a view, do so when querying the view and not when Creating it. This error can also occur in SQL*Forms Applications if a continuation line is indented. Check for indented Lines and delete These Spaces.

  • You can send me your query?

  • I edited in the description.

  • could see Developer?

  • Can you create this structure in sqlfiddle.com? I’m trying to solve another issue posted here http://answall.com/questions/127503/listar-registries-que-est%C3%A3o-em-uma-tabela-e-os-que-n%C3%A3o-est%C3%A3o-na-mesma-tabela/127609#127609, this sql helped a lot. No need to create with all fields, only minimal needed with some records, it will be easier to help you.

Show 4 more comments

Browser other questions tagged sql oracle

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