tried a query with union but did not get the expected result

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 execute it only shows the MOBI 'column. Below is 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   
    
asked by anonymous 02.08.2017 / 19:40

3 answers

2

It has an extra parenthesis in its JOIN with table pessoa . Here's how:

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

The correct one is:

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

As for the result of query , to return the two columns you must define it in the two parts of query . At 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
    
02.08.2017 / 19:57
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   
    
02.08.2017 / 19:55
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 column names will always have the name of the 1 query, as you put the column MOBI and DESMOBI in the same position, as the 1 column is MOBI is the name of the column that will stay.

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 number of columns and the two columns you want to appear.

    
02.08.2017 / 20:05