I tried a Union consultation but did not get the expected result

Asked

Viewed 55 times

1

I made a query with two selects using union and it has two columns: MOBI and DESMOBI. I need the result to show these two columns. when I run it shows only the column MOBI`. Below follows the query. I look forward to your help.

SELECT 
        RE.cd_flremequ AS RemessaID
       ,DE.cd_flremequ AS DevID
       ,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
       ,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
       ,F.numero AS ficha
       ,R.sequencia AS num_remessa
       ,D.sequencia AS num_devolucao
       ,SUM(RE.qt_remessa) AS qt_remessa
       ,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
       ,RE.vl_uni_locacao
       ,SUM(RE.qt_remessa) * RE.vl_uni_locacao AS MOBI
       ,G.CD_GRUPO
       ,F.cd_pessoa_fun AS cod_vendedor
       ,VENDEDOR.nm_pessoa AS vendedor
       ,E.nm_equipto
       ,P.nr_patrimonio
       ,F.cd_pessoa AS cod_cli
       ,C.nm_pessoa


FROM 
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo

 WHERE 
       (R.dt_saida IS NOT NULL AND R.dt_saida_sis IS NOT NULL)
        AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/
         AND ((R.dt_saida between '2017-01-01' AND '2017-07-31'))
          /*AND fl_equipto_pesado = 'S' */
           AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)


GROUP BY
        RE.cd_flremequ
       ,DE.cd_flremequ
       ,P.nr_patrimonio
       ,R.dt_saida
       ,F.numero
       ,R.sequencia
       ,RE.qt_remessa
       ,RE.vl_uni_locacao
       ,DE.qt_devolucao
       ,RE.vl_uni_locacao
       ,RE.cd_pessoa
       ,E.nm_equipto
       ,C.nm_pessoa
       ,F.cd_pessoa_fun
       ,F.cd_pessoa
       ,vendedor.nm_pessoa
       ,D.sequencia
       ,E.CD_GRUPO
       ,G.CD_GRUPO
       ,e.fl_pat_equipto
       ,D.dt_cobranca
       ,RE.qt_devolucao



UNION 

SELECT 
     RE.cd_flremequ AS RemessaID
       ,DE.cd_flremequ AS DevID
       ,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
       ,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
       ,F.numero AS ficha
       ,R.sequencia AS num_remessa
       ,D.sequencia AS num_devolucao
       ,SUM(RE.qt_remessa) AS qt_remessa
       ,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
       ,RE.vl_uni_locacao
       ,SUM(DE.qt_devolucao * RE.vl_uni_locacao) AS 'DESMOBI'
       ,G.CD_GRUPO
       ,F.cd_pessoa_fun AS cod_vendedor
       ,vendedor.nm_pessoa AS vendedor
       ,E.nm_equipto
       ,P.nr_patrimonio
       ,F.cd_pessoa AS cod_cli
       ,C.nm_pessoa

FROM 
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo

WHERE (D.dt_cobranca between '2017-01-01' AND '2017-07-31')
       AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)
        AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/


GROUP BY 
        RE.cd_flremequ
       ,DE.cd_flremequ
       ,P.nr_patrimonio
       ,R.dt_saida
       ,F.numero
       ,R.sequencia
       ,RE.qt_remessa
       ,RE.vl_uni_locacao
       ,DE.qt_devolucao
       ,RE.vl_uni_locacao
       ,RE.cd_pessoa
       ,E.nm_equipto
       ,C.nm_pessoa
       ,F.cd_pessoa_fun
       ,F.cd_pessoa
       ,vendedor.nm_pessoa
       ,D.sequencia
       ,E.CD_GRUPO
       ,G.CD_GRUPO
       ,e.fl_pat_equipto
       ,D.dt_cobranca
       ,RE.qt_devolucao 

ORDER BY F.numero DESC
sql database union union-all

  • It’s a bit messy, hard to understand, a tidy up with the code tags

  • I’m new here, I clean up.?

  • This query returns something when executed together with Union?

  • Click edit, select your code, and type Ctrl+k

  • the column name will always be defined in the first query, if you want two different names, only do it in two columns

  • thanks @Marcosmarques for the edition

  • @Geiltonxavier works yes only that the columns mobi and desmobi saw only one. I need the result of the two columns

  • Which DBMS you are using?

  • @Janderhelius solved your problem ?

  • Opa @rovann-linhalis. I solved yes. Thank you very much!

  • Mark any of the answers to finish the question

Show 6 more comments

3 answers

2

You have an extra parenthesis in your JOIN with the table pessoa. It’s like this:

...
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
...

The correct is:

...
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun
...

As to the outcome of query, to return the two columns you must define it in the two parts of the query. In the first add the column DESMOBI:

...
,RE.vl_uni_locacao
,SUM(RE.qt_remessa) * RE.vl_uni_locacao AS MOBI
,NULL AS DESMOBI
,G.CD_GRUPO
...

And in the second add the column MOBI:

...
,RE.vl_uni_locacao
,NULL AS MOBI
,SUM(DE.qt_devolucao * RE.vl_uni_locacao) AS 'DESMOBI'
,G.CD_GRUPO
...

The resulting query is as follows:

SELECT re.cd_flremequ AS remessaid,
       de.cd_flremequ AS devid,
       CONVERT(varchar(10), r.dt_saida, 111)AS dt_saida,
       CONVERT(varchar(10), MAX(d.dt_cobranca), 111) AS dt_devolucao,
       f.numero AS ficha,
       r.sequencia AS num_remessa,
       d.sequencia AS num_devolucao,
       SUM(re.qt_remessa) AS qt_remessa,
       COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev,
       re.vl_uni_locacao,
       SUM(re.qt_remessa) * re.vl_uni_locacao AS mobi,
       NULL AS desmobi,
       g.cd_grupo,
       f.cd_pessoa_fun AS cod_vendedor,
       vendedor.nm_pessoa AS vendedor,
       e.nm_equipto,
       p.nr_patrimonio,
       f.cd_pessoa AS cod_cli,
       c.nm_pessoa
  FROM fich_loc AS f
       LEFT OUTER JOIN pessoa AS c ON f.cd_pessoa = c.cd_pessoa
       LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = f.cd_pessoa_fun
       LEFT OUTER JOIN fl_remessa AS r ON f.cd_controle = r.cd_controle
       LEFT OUTER JOIN fl_rem_equ AS re ON r.cd_flremessa = re.cd_flremessa
       LEFT OUTER JOIN equipto AS e ON re.cd_equipto = e.cd_equipto
       LEFT OUTER JOIN fl_dev_equ AS de ON re.cd_flremequ = de.cd_flremequ
       LEFT OUTER JOIN fl_devolucao AS d ON de.cd_fldevolucao = d.cd_fldevolucao
       LEFT OUTER JOIN patrimon AS p ON re.cd_patrimonio = p.cd_patrimonio
       LEFT OUTER JOIN grupo AS g ON e.cd_grupo = g.cd_grupo
 WHERE r.dt_saida IS NOT NULL
   AND r.dt_saida_sis IS NOT NULL
   AND re.vl_uni_locacao > '0' /*ELIMINA VENDA LOC*/
   AND r.dt_saida BETWEEN '2017-01-01' AND '2017-07-31'
   /*AND fl_equipto_pesado = 'S' */
   AND g.cd_grupo IN (2, 3, 6, 21, 44, 47, 53, 54, 71)
 GROUP BY re.cd_flremequ,
          de.cd_flremequ,
          p.nr_patrimonio,
          r.dt_saida,
          f.numero,
          r.sequencia,
          re.qt_remessa,
          re.vl_uni_locacao,
          de.qt_devolucao,
          re.vl_uni_locacao,
          re.cd_pessoa,
          e.nm_equipto,
          c.nm_pessoa,
          f.cd_pessoa_fun,
          f.cd_pessoa,
          vendedor.nm_pessoa,
          d.sequencia,
          e.cd_grupo,
          g.cd_grupo,
          e.fl_pat_equipto,
          d.dt_cobranca,
          re.qt_devolucao

UNION

SELECT re.cd_flremequ AS remessaid,
       de.cd_flremequ AS devid,
       CONVERT(varchar(10), r.dt_saida, 111)AS dt_saida,
       CONVERT(varchar(10), MAX(d.dt_cobranca), 111) AS dt_devolucao,
       f.numero AS ficha,
       r.sequencia AS num_remessa,
       d.sequencia AS num_devolucao,
       SUM(re.qt_remessa) AS qt_remessa,
       COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev,
       re.vl_uni_locacao,
       NULL AS mobi,
       SUM(de.qt_devolucao * re.vl_uni_locacao) AS 'DESMOBI',
       g.cd_grupo,
       f.cd_pessoa_fun AS cod_vendedor,
       vendedor.nm_pessoa AS vendedor,
       e.nm_equipto,
       p.nr_patrimonio,
       f.cd_pessoa AS cod_cli,
       c.nm_pessoa
  FROM fich_loc AS f
       LEFT OUTER JOIN pessoa AS c ON f.cd_pessoa = c.cd_pessoa
       LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = f.cd_pessoa_fun
       LEFT OUTER JOIN fl_remessa AS r ON f.cd_controle = r.cd_controle
       LEFT OUTER JOIN fl_rem_equ AS re ON r.cd_flremessa = re.cd_flremessa
       LEFT OUTER JOIN equipto AS e ON re.cd_equipto = e.cd_equipto
       LEFT OUTER JOIN fl_dev_equ AS de ON re.cd_flremequ = de.cd_flremequ
       LEFT OUTER JOIN fl_devolucao AS d ON de.cd_fldevolucao = d.cd_fldevolucao
       LEFT OUTER JOIN patrimon AS p ON re.cd_patrimonio = p.cd_patrimonio
       LEFT OUTER JOIN grupo AS g ON e.cd_grupo = g.cd_grupo
 WHERE d.dt_cobranca BETWEEN '2017-01-01' AND '2017-07-31'
   AND g.cd_grupo IN (2, 3, 6, 21, 44, 47, 53, 54, 71)
   AND re.vl_uni_locacao > '0' /*ELIMINA VENDA LOC*/
 GROUP BY re.cd_flremequ,
          de.cd_flremequ,
          p.nr_patrimonio,
          r.dt_saida,
          f.numero,
          r.sequencia,
          re.qt_remessa,
          re.vl_uni_locacao,
          de.qt_devolucao,
          re.vl_uni_locacao,
          re.cd_pessoa,
          e.nm_equipto,
          c.nm_pessoa,
          f.cd_pessoa_fun,
          f.cd_pessoa,
          vendedor.nm_pessoa,
          d.sequencia,
          e.cd_grupo,
          g.cd_grupo,
          e.fl_pat_equipto,
          d.dt_cobranca,
          re.qt_devolucao
 ORDER BY f.numero DESC

1

SELECT RE.cd_flremequ AS RemessaID ,
DE.cd_flremequ AS DevID ,
CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida ,
CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao ,
F.numero AS ficha ,
R.sequencia AS num_remessa ,
D.sequencia AS num_devolucao ,
SUM(RE.qt_remessa) AS qt_remessa ,
COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev ,
RE.vl_uni_locacao ,
SUM(RE.qt_remessa) * RE.vl_uni_locacao AS MOBI , 
0 AS 'DESMOBI',
G.CD_GRUPO ,
F.cd_pessoa_fun AS cod_vendedor ,
VENDEDOR.nm_pessoa AS vendedor ,
E.nm_equipto ,P.nr_patrimonio ,
F.cd_pessoa AS cod_cli ,
C.nm_pessoa
FROM (((((((((((fich_loc AS F LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa) LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle) LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa) LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto) LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ) LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao) LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio) LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo)))
WHERE (R.dt_saida IS NOT NULL AND R.dt_saida_sis IS NOT NULL) AND RE.vl_uni_locacao >'0' AND ((R.dt_saida between '2017-01-01' AND '2017-07-31')) /*AND fl_equipto_pesado = 'S' */ AND G.cd_grupo in (2,3,6,21,44,47,53,54,71) --and fl_equipto_pesado = 'S' -- AND F.numero = '3491' --AND R.sequencia = '4' and F.cd_pessoa = '3491'
GROUP BY RE.cd_flremequ ,DE.cd_flremequ ,P.nr_patrimonio ,R.dt_saida ,F.numero ,R.sequencia ,RE.qt_remessa ,RE.vl_uni_locacao ,DE.qt_devolucao ,RE.vl_uni_locacao ,RE.cd_pessoa ,E.nm_equipto ,C.nm_pessoa ,F.cd_pessoa_fun ,F.cd_pessoa ,vendedor.nm_pessoa ,D.sequencia ,E.CD_GRUPO ,G.CD_GRUPO ,e.fl_pat_equipto ,D.dt_cobranca ,RE.qt_devolucao

UNION

SELECT RE.cd_flremequ AS RemessaID ,
DE.cd_flremequ AS DevID ,
CONVERT(VARCHAR(10),
R.dt_saida,111)AS dt_saida ,
CONVERT(VARCHAR(10),
 MAX(D.dt_cobranca),111) AS dt_devolucao ,
 F.numero AS ficha ,
 R.sequencia AS num_remessa ,
 D.sequencia AS num_devolucao ,
 SUM(RE.qt_remessa) AS qt_remessa ,
 COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev ,
 RE.vl_uni_locacao ,
 0 as 'MOBI',
 SUM(DE.qt_devolucao * RE.vl_uni_locacao) AS 'DESMOBI' ,
 G.CD_GRUPO ,F.cd_pessoa_fun AS cod_vendedor ,
 vendedor.nm_pessoa AS vendedor ,
 E.nm_equipto ,P.nr_patrimonio ,
 F.cd_pessoa AS cod_cli ,
 C.nm_pessoa

FROM (((((((((((fich_loc AS F LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa) LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle) LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa) LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto) LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ) LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao) LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio) LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo)))

WHERE (D.dt_cobranca between '2017-01-01' AND '2017-07-31') AND G.cd_grupo in (2,3,6,21,44,47,53,54,71) AND RE.vl_uni_locacao >'0' --/ELIMINA VENDA LOC/ --AND F.numero = '3491' --AND D.sequencia = '2' AND F.cd_pessoa = '3491'

GROUP BY RE.cd_flremequ ,DE.cd_flremequ ,P.nr_patrimonio ,R.dt_saida ,F.numero ,R.sequencia ,RE.qt_remessa ,RE.vl_uni_locacao ,DE.qt_devolucao ,RE.vl_uni_locacao ,RE.cd_pessoa ,E.nm_equipto ,C.nm_pessoa ,F.cd_pessoa_fun ,F.cd_pessoa ,vendedor.nm_pessoa ,D.sequencia ,E.CD_GRUPO ,G.CD_GRUPO ,e.fl_pat_equipto ,D.dt_cobranca ,RE.qt_devolucao

ORDER BY F.numero DESC

This is a very common mistake, Union has to have the number of columns in each select equal. And the names of the columns will always have the name of the 1 query, as you placed the column MOBI and DESMOBI in the same position, as the 1 column is MOBI is the name of the column that will be.

I put in the first query DESMOBI with value 0 and in the second query MOBI value ZERO.

So the two queries have the same amount of columns and the two columns you want to appear.

1

If you want two different columns, you have to separate them, where in the opposite query the value comes null, see if it helps:

    /*SELECT DA REMESSA*/

SELECT 
    RE.cd_flremequ AS RemessaID
   ,DE.cd_flremequ AS DevID
   ,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
   ,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
   ,F.numero AS ficha
   ,R.sequencia AS num_remessa
   ,D.sequencia AS num_devolucao
   ,SUM(RE.qt_remessa) AS qt_remessa
   ,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
   ,RE.vl_uni_locacao
   ,SUM(RE.qt_remessa) * RE.vl_uni_locacao AS MOBI
   ,NULL AS 'DESMOBI'
   ,G.CD_GRUPO
   ,F.cd_pessoa_fun AS cod_vendedor
   ,VENDEDOR.nm_pessoa AS vendedor
   ,E.nm_equipto
   ,P.nr_patrimonio
   ,F.cd_pessoa AS cod_cli
   ,C.nm_pessoa


FROM 
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo

 WHERE 
       (R.dt_saida IS NOT NULL AND R.dt_saida_sis IS NOT NULL)
        AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/
         AND ((R.dt_saida between '2017-01-01' AND '2017-07-31'))
          /*AND fl_equipto_pesado = 'S' */
           AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)


GROUP BY
        RE.cd_flremequ
       ,DE.cd_flremequ
       ,P.nr_patrimonio
       ,R.dt_saida
       ,F.numero
       ,R.sequencia
       ,RE.qt_remessa
       ,RE.vl_uni_locacao
       ,DE.qt_devolucao
       ,RE.vl_uni_locacao
       ,RE.cd_pessoa
       ,E.nm_equipto
       ,C.nm_pessoa
       ,F.cd_pessoa_fun
       ,F.cd_pessoa
       ,vendedor.nm_pessoa
       ,D.sequencia
       ,E.CD_GRUPO
       ,G.CD_GRUPO
       ,e.fl_pat_equipto
       ,D.dt_cobranca
       ,RE.qt_devolucao



UNION 

/*SELECT DA DEVOLUÇÃO*/

SELECT 
     RE.cd_flremequ AS RemessaID
       ,DE.cd_flremequ AS DevID
       ,CONVERT(VARCHAR(10), R.dt_saida,111)AS dt_saida
       ,CONVERT(VARCHAR(10), MAX(D.dt_cobranca),111) AS dt_devolucao
       ,F.numero AS ficha
       ,R.sequencia AS num_remessa
       ,D.sequencia AS num_devolucao
       ,SUM(RE.qt_remessa) AS qt_remessa
       ,COALESCE(SUM(de.qt_devolucao), 0) AS qt_dev
       ,RE.vl_uni_locacao
       NULL as 'MOBI'
       ,SUM(DE.qt_devolucao * RE.vl_uni_locacao) AS 'DESMOBI'
       ,G.CD_GRUPO
       ,F.cd_pessoa_fun AS cod_vendedor
       ,vendedor.nm_pessoa AS vendedor
       ,E.nm_equipto
       ,P.nr_patrimonio
       ,F.cd_pessoa AS cod_cli
       ,C.nm_pessoa

FROM 
fich_loc AS F
LEFT OUTER JOIN pessoa AS C ON F.cd_pessoa = C.cd_pessoa
LEFT OUTER JOIN pessoa AS vendedor ON vendedor.cd_pessoa = F.cd_pessoa_fun)
LEFT OUTER JOIN fl_remessa AS R ON F.cd_controle = R.cd_controle
LEFT OUTER JOIN fl_rem_equ AS RE ON R.cd_flremessa= RE.cd_flremessa
LEFT OUTER JOIN equipto AS E ON RE.cd_equipto = E.cd_equipto
LEFT OUTER JOIN fl_dev_equ AS DE ON RE.cd_flremequ = DE.cd_flremequ
LEFT OUTER JOIN fl_devolucao AS D ON DE.cd_fldevolucao = D.cd_fldevolucao
LEFT OUTER JOIN patrimon AS P ON RE.cd_patrimonio = P.cd_patrimonio
LEFT OUTER JOIN grupo AS G ON E.cd_grupo = G.cd_grupo

WHERE (D.dt_cobranca between '2017-01-01' AND '2017-07-31')
       AND G.cd_grupo in (2,3,6,21,44,47,53,54,71)
        AND RE.vl_uni_locacao >'0' /*ELIMINA VENDA LOC*/


GROUP BY 
        RE.cd_flremequ
       ,DE.cd_flremequ
   ,P.nr_patrimonio
   ,R.dt_saida
   ,F.numero
   ,R.sequencia
   ,RE.qt_remessa
   ,RE.vl_uni_locacao
   ,DE.qt_devolucao
   ,RE.vl_uni_locacao
   ,RE.cd_pessoa
   ,E.nm_equipto
   ,C.nm_pessoa
   ,F.cd_pessoa_fun
   ,F.cd_pessoa
   ,vendedor.nm_pessoa
   ,D.sequencia
   ,E.CD_GRUPO
   ,G.CD_GRUPO
   ,e.fl_pat_equipto
   ,D.dt_cobranca
   ,RE.qt_devolucao 

ORDER BY F.numero DESC

Browser other questions tagged sql database union union-all

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