UNION - SQL Server

Asked

Viewed 208 times

1

I’m trying to make an appointment with Union, and it returns the following error message:

Message 5335, Level 16, Status 1, Line 1 The type of text data cannot be used as an operand for UNION, INTERSECT or EXCEPT operators because it is not comparable.

But when using the Union all, the query is executed.

Would someone please give me a more detailed explanation of why this occurs?

Follow the select below:

select A.ID,
       C.QDADE Quantidade,
       C.VALLIQUIDO ValLiquido,
       C.VALUNITARIO ValUnit,
       E.NRODOCTO Docto,
       E.DOCTODIGITADO DoctoDigitado,
       F.CODIGO CodFilial,
       H.IDENTIFICACAO Cnpj,
       H.NOME NOMEFil,
       H.FANTASIA Fantasia,
       E.DATAEMI Emissao,
       I.NMPROD NmProd,
       I.CODPROD CodProd,
       K.CODIGO Unid,      
       L.COMPLEMENTO Servico,
       N.NOME FamiliaServico,
       N.CODIGO CodFamServico
from M3_ITPRONTCOM A
INNER JOIN M3_ITEMNOTCOM B ON B.ID = A.IDA
INNER JOIN M3_DOCTOITEM C ON C.ID = B.IDA
INNER JOIN M3_PARTEDOCTO D ON D.ID = C.IDA
INNER JOIN M3_DOCTO E ON E.ID = D.IDF_DOCTO
INNER JOIN M3_FILIAL F ON F.ID = E.IDF_FILIAL
INNER JOIN MTMG_PAPELREL G ON G.ID = F.IDA
INNER JOIN MTMG_IDENT H ON H.ID = G.IDF_IDENT
INNER JOIN M3_EMPRESA O ON O.ID = F.IDF_EMPRESA
LEFT  JOIN M3_PROD I ON I.ID = C.IDF_PRODUTO
LEFT  JOIN M3_UNPROD J ON J.ID = C.IDF_UNIDADEPROD
LEFT  JOIN M3_UNIDADE K ON K.ID = J.IDF_UNIDADE
LEFT  JOIN M3_SERVICO L ON L.ID = C.IDF_SERVICO
LEFT  JOIN M3_FAMGRUSERV M ON M.ID = L.IDF_FAMGRUPOSERVICO
LEFT  JOIN M3_FAMSERVICO N ON N.ID = M.IDA

union 

select A1.ID,
       C1.QDADE Quantidade,
       C1.VALLIQUIDO ValLiquido,
       C1.VALUNITARIO ValUnit,
       E1.NRODOCTO Docto,
       E1.DOCTODIGITADO DoctoDigitado,
       F1.CODIGO CodFilial,
       H1.IDENTIFICACAO Cnpj,
       H1.NOME NomeFil,
       H1.FANTASIA Fantasia,
       E1.DATAEMI Emissao,
       I1.NMPROD NmProd,
       I1.CODPROD CodProd,
       K1.CODIGO Unid,      
       L1.COMPLEMENTO Servico,
       N1.NOME FamiliaServico,
       N1.CODIGO CodFamServico
from M3_ITSERNTCOM A1
INNER JOIN M3_ITEMNOTCOM B1 ON B1.ID = A1.IDA
INNER JOIN M3_DOCTOITEM C1 ON C1.ID = B1.IDA
INNER JOIN M3_PARTEDOCTO D1 ON D1.ID = C1.IDA
INNER JOIN M3_DOCTO E1 ON E1.ID = D1.IDF_DOCTO
INNER JOIN M3_FILIAL F1 ON F1.ID = E1.IDF_FILIAL
INNER JOIN MTMG_PAPELREL G1 ON G1.ID = F1.IDA
INNER JOIN MTMG_IDENT H1 ON H1.ID = G1.IDF_IDENT
INNER JOIN M3_EMPRESA O1 ON O1.ID = F1.IDF_EMPRESA
LEFT  JOIN M3_PROD I1 ON I1.ID = C1.IDF_PRODUTO
LEFT  JOIN M3_UNPROD J1 ON J1.ID = C1.IDF_UNIDADEPROD
LEFT  JOIN M3_UNIDADE K1 ON K1.ID = J1.IDF_UNIDADE
LEFT  JOIN M3_SERVICO L1 ON L1.ID = C1.IDF_SERVICO
LEFT  JOIN M3_FAMGRUSERV M1 ON M1.ID = L1.IDF_FAMGRUPOSERVICO
LEFT  JOIN M3_FAMSERVICO N1 ON N1.ID = M1.IDA
sql select union

1 answer

3


The command Union eliminates repetitions, as explained in the message:

The type of text data cannot be used [...] because it is not comparable.

and for the elimination of repetitions, it is necessary to comparison.

Already Union all makes no comparison, so there are no problems with its use.

Browser other questions tagged sql select union

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