Fazendo união e agrupamento de duas consultas SQL
Tenho duas consultas SQL, que vão numa base do Oracle mas funcionam pelo SQL Server:
Consulta 1
SELECT IDHOTEL AS idhotel, RAZAOSOCIAL AS razaosocial, NOSSONUMERO AS nossonumero,
NUMERODOCUMENTO AS numerodocumento, CAST(VALOR AS NUMERIC(15,2)) AS valor, END1 AS end1, END2 AS end2,
replace(convert(NVARCHAR, DATAEMISSAO, 103), '-', '/') AS dataemissao, replace(convert(NVARCHAR, DATAPROGRAMADA, 103), '-', '/') AS datavencimento,
HISTORICOCOMPL AS historico, NUMEROBOLETO AS numeroboleto, NUMLEITOR AS numleitor, (SUBSTRING(CODCONTA,1,4)+'/'+SUBSTRING(CODCONTA,5,5)+'-'+SUBSTRING(CODCONTA,10,2)) AS codconta, NOSSONUMERO AS nossonumero, STATUS AS status FROM OPENQUERY
(CM_ORACLE,'SELECT DISTINCT D.IDHOTEL AS IDHOTEL, P.RAZAOSOCIAL AS RazaoSocial, (E.LOGRADOURO || '', '' || E.NUMERO || '' - '' || E.COMPLEMENTO) AS END1, (E.BAIRRO || '' - '' || SUBSTR(E.CEP,1,5) || ''-'' || SUBSTR(E.CEP,6,3) || '' - '' || C.NOME || ''/'' || C.UF ) AS END2,
P.NOME AS Nome, D.DATAPROGRAMADA AS DataProgramada,
ROUND(L.VALOR,2) AS Valor, L.HISTORICOCOMPL AS HistoricoCompl, D.NODOCUMENTO || '' '' || D.COMPLDOCUMENTO AS NumeroDocumento, D.NUMDIGCODBARRAS AS NumeroBoleto, D.NUMLEITCODBARRAS AS NumLeitor,
D.DATAEMISSAO AS DataEmissao, D.DATAVENCTO AS DataVencimento, PT.NUMEMPRESABANCO AS CodConta, L.DATALANCTO AS DataLancamento, D.NOSSONUMERO AS NossoNumero,
S.SALDO AS Saldo, (CASE D.STATUS WHEN ''1'' THEN ''PENDENTE'' ELSE ''PAGO'' END) AS Status, D.CODDOCUMENTO AS CodDocumento, D.NUMFATURA AS NumeroFatura FROM
DOCUMENTO D, PESSOA P, LANCTODOCUM L, ENDPESS E, CIDADES C, PORTADORFORMA PT, ( SELECT (0) AS CODDOCUMENTO, (0.00) AS SALDO FROM DUAL ) S, TIPODOCRECPAG WHERE
( D.IDFORCLI = P.IDPESSOA ) AND ( P.IDPESSOA = E.IDPESSOA ) AND ( E.IDCIDADES = C.IDCIDADES ) AND ( D.CODDOCUMENTO = L.CODDOCUMENTO ) AND ( D.OPERACAO = L.OPERACAO ) AND ( D.CODPORTFORMA = PT.CODPORTFORMA(+) ) AND
( ((RTRIM(L.HISTORICOCOMPL) <> ''ESTORNO'') OR (L.HISTORICOCOMPL IS NULL)) ) AND ( TIPODOCRECPAG.CODTIPDOC=D.CODTIPDOC ) AND ( D.RECPAG = ''R'' ) AND
( EXISTS (SELECT CODTIPDOC FROM TIPODOCRECPAG A WHERE A.RECPAG = ''R'' AND A.CODTIPDOC = TIPODOCRECPAG.CODTIPDOC AND NOT EXISTS ( SELECT 1 FROM USUARIOXTPDOCTO B
WHERE RECPAG = ''R'' AND B.IDUSUARIO = 80664974) UNION SELECT CODTIPDOC FROM TIPODOCRECPAG A WHERE A.RECPAG = ''R''
AND A.CODTIPDOC = TIPODOCRECPAG.CODTIPDOC AND EXISTS (SELECT 1 FROM USUARIOXTPDOCTO B
WHERE RECPAG = ''R'' AND A.CODTIPDOC = B.CODTIPDOC AND B.IDUSUARIO = 80664974) ) ) AND
(D.DATAPROGRAMADA > TO_DATE(''01/01/2015'', ''dd/mm/yyyy''))
ORDER BY RazaoSocial ASC') WHERE IDHOTEL = 239 AND NOSSONUMERO <> '' AND DATAPROGRAMADA
BETWEEN (cast(2015 as varchar(4)) + '-' + cast(07 as varchar(2)) + '-' + cast(01 as varchar(2)))
AND (cast(2015 as varchar(4)) + '-' + cast(07 as varchar(2)) + '-' + cast(31 as varchar(2))) AND (HISTORICOCOMPL LIKE '%capital%' OR HISTORICOCOMPL LIKE '%giro%' OR HISTORICOCOMPL LIKE '%pré%')
Consulta 2
SELECT IDHOTEL,RazaoSocial,CAST(TotalPago AS NUMERIC(15,2)) AS TotalPago,CAST(TotalPendente AS NUMERIC(15,2)) AS TotalPendente
FROM OPENQUERY (CM_ORACLE,'SELECT DISTINCT
D.IDHOTEL,
P.RAZAOSOCIAL AS RazaoSocial,
SUM(ROUND((CASE D.STATUS WHEN ''1'' THEN L.VALOR ELSE 0 END),2)) AS TotalPendente,
SUM(ROUND((CASE D.STATUS WHEN ''1'' THEN 0 ELSE L.VALOR END),2)) AS TotalPago
FROM
DOCUMENTO D,
PESSOA P,
LANCTODOCUM L,
ENDPESS E,
CIDADES C,
PORTADORFORMA PT,
( SELECT (0) AS CODDOCUMENTO, (0.00) AS SALDO FROM DUAL ) S,
TIPODOCRECPAG
WHERE
( D.IDFORCLI = P.IDPESSOA ) AND
( P.IDPESSOA = E.IDPESSOA ) AND
( E.IDCIDADES = C.IDCIDADES ) AND
( D.CODDOCUMENTO = L.CODDOCUMENTO ) AND
( D.OPERACAO = L.OPERACAO ) AND
( D.CODPORTFORMA = PT.CODPORTFORMA(+) ) AND
( ((RTRIM(L.HISTORICOCOMPL) <> ''ESTORNO'') OR (L.HISTORICOCOMPL IS NULL)) ) AND
( TIPODOCRECPAG.CODTIPDOC=D.CODTIPDOC ) AND
( D.RECPAG = ''R'' ) AND
( EXISTS (SELECT CODTIPDOC FROM TIPODOCRECPAG A WHERE A.RECPAG = ''R''
AND A.CODTIPDOC = TIPODOCRECPAG.CODTIPDOC AND NOT EXISTS ( SELECT 1 FROM USUARIOXTPDOCTO B
WHERE RECPAG = ''R'' AND B.IDUSUARIO = 80664974) UNION
SELECT CODTIPDOC FROM TIPODOCRECPAG A WHERE A.RECPAG = ''R''
AND A.CODTIPDOC = TIPODOCRECPAG.CODTIPDOC AND EXISTS (SELECT 1 FROM USUARIOXTPDOCTO B
WHERE RECPAG = ''R'' AND A.CODTIPDOC = B.CODTIPDOC AND B.IDUSUARIO = 80664974) ) ) AND
(D.NOSSONUMERO <> ''NULL'') AND
(D.DATAPROGRAMADA >= TO_DATE(''01/01/2014'', ''dd/mm/yyyy''))
GROUP BY D.IDHOTEL,RazaoSocial
ORDER BY RazaoSocial ASC') WHERE IDHOTEL = 239
Eu queria poder agrupar os dados das duas consultas pelos campos IDHotel e RazãoSocial, que existem em ambas.
É possível fazer isso? Tentei com UNION, mas aí ele me dá sempre duas linhas diferentes.
Discussão (3)
Carregando comentários...