subconsultas retornam 2 linhas
18/12/2013
0
SELECT DISTINCT
TIPO AS TIPO,
DANFE AS DANFE,
DATA_EMISSAO AS DATA_EMISSAO,
DT_LANC_SISTEMA AS DT_LANC_SISTEMA,
FORNECEDOR AS FORNECEDOR,
LOJA AS LOJA,
RAZAO AS RAZAO,
COD_PRODUTO AS COD_PRODUTO,
DESCRICAO AS DESCRICAO,
VALOR_PROD AS VALOR_PROD,
VALOR_DANFE AS VALOR_DANFE,
NATUREZA AS NATUREZA,
DESC_NAT AS DESC_NAT
FROM (
SELECT DISTINCT
D1_TIPO AS TIPO,
F1_DOC AS DANFE,
F1_EMISSAO AS DATA_EMISSAO,
F1_DTDIGIT AS DT_LANC_SISTEMA,
F1_FORNECE AS FORNECEDOR,
F1_LOJA AS LOJA,
A2_NOME AS RAZAO,
D1_COD AS COD_PRODUTO,
B1_DESC AS DESCRICAO,
D1_TOTAL AS VALOR_PROD,
F1_VALBRUT AS VALOR_DANFE,
'' AS NATUREZA,
'' AS DESC_NAT
FROM SD1010 JOIN SF1010 ON D1_DOC = F1_DOC
AND D1_SERIE = F1_SERIE
AND D1_FORNECE = F1_FORNECE
AND D1_LOJA = F1_LOJA
JOIN SB1010 ON B1_FILIAL = ' '
AND B1_COD = D1_COD
JOIN SA2010 ON A2_COD = F1_FORNECE
AND A2_LOJA = F1_LOJA
WHERE D1_DTDIGIT >= '20131201'--RIGHT(Convert(varchar,:DataDe),4)+SUBSTRING(Convert(varchar,:DataDe),3,2)+LEFT(Convert(varchar,:DataDe),2)
AND D1_DTDIGIT <= '20131217'--RIGHT(Convert(varchar,:DataDe),4)+SUBSTRING(Convert(varchar,:DataDe),3,2)+LEFT(Convert(varchar,:DataDe),2)
AND D1_TIPO = 'N'
AND SD1010.D_E_L_E_T_ <>'*'
AND SB1010.D_E_L_E_T_ <>'*'
AND SF1010.D_E_L_E_T_ <>'*'
AND SA2010.D_E_L_E_T_ <>'*'
GROUP BY D1_TIPO,F1_DOC,F1_FORNECE,F1_LOJA,A2_NOME, D1_TIPO,D1_COD, B1_DESC, F1_VALBRUT,F1_EMISSAO,F1_DTDIGIT
, F1_VALMERC,B1_GRUPO, D1_TOTAL
UNION ALL
SELECT DISTINCT
'',
E2_NUM AS DANFE,
'',
'',
E2_FORNECE AS FORNECEDOR,
E2_LOJA AS LOJA,
'',
'',
'',
'',
'',
E2_NATUREZ AS NATUREZA,
ED_DESCRIC AS DESC_NAT
FROM SE2010 INNER JOIN SF1010 ON E2_NUM = F1_DOC
AND E2_FORNECE = F1_FORNECE
AND E2_LOJA = F1_LOJA
INNER JOIN SED010 ON ED_CODIGO = E2_NATUREZ
INNER JOIN SD1010 ON D1_DOC = F1_DOC
AND D1_SERIE = F1_SERIE
AND D1_FORNECE = F1_FORNECE
AND D1_FILIAL = F1_FILIAL
WHERE E2_EMIS1 >= '20131201'
AND E2_EMIS1 <= '20131217'
AND SE2010.D_E_L_E_T_ <> '*'
AND SED010.D_E_L_E_T_ <> '*'
--AND E2_NUM IN ('000005018','000005970')
GROUP BY E2_NUM, E2_FORNECE, E2_LOJA, E2_NATUREZ, ED_DESCRIC
) AS A INNER JOIN SA2010 ON A.FORNECEDOR = A2_COD
AND A.LOJA = A2_LOJA
--) AS A INNER JOIN SB1010 ON A.COD_PRODUTO = B1_COD
GROUP BY A.DANFE,A.TIPO,A.DATA_EMISSAO,A.DT_LANC_SISTEMA, A.FORNECEDOR,A.LOJA,A.RAZAO,
A.COD_PRODUTO,A.DESCRICAO, A.VALOR_DANFE, A.VALOR_PROD, A.NATUREZA, A.DESC_NAT
Segue abaixo o resultado de um exemplo do resultado da consulta
TIPO DANFE FORNECEDOR LOJA RAZAO COD_PRODUTO DESCRICAO VALOR_PROD VALOR_DANFE NATUREZA
3 1910 1 0 0 10.101.09
N 3 1910 1 FLAVIA 300082 CONFECCAO 640 640
Desde já obrigado
Alexandre
Posts
18/12/2013
Alexandre
18/12/2013
Isaac Jose
tente colocar o max nesses campos ai ele ira trazer somente o max de cada coluna.....
19/12/2013
Cléverson Specht
Pelo que analisei está duplicando as linhas pelo retorno desse select:
SELECT DISTINCT '', E2_NUM AS DANFE, '', '', E2_FORNECE AS FORNECEDOR, E2_LOJA AS LOJA, '', '', '', '', '', E2_NATUREZ AS NATUREZA, ED_DESCRIC AS DESC_NAT FROM SE2010 ...
Alterei a query para que traga o max e o sum de alguns campos corrigindo os campos vazios retornados:
SELECT DISTINCT MAX(TIPO) AS TIPO, DANFE AS DANFE, MAX(DATA_EMISSAO) AS DATA_EMISSAO, MAX(DT_LANC_SISTEMA) AS DT_LANC_SISTEMA, FORNECEDOR AS FORNECEDOR, LOJA AS LOJA, MAX(RAZAO) AS RAZAO, MAX(COD_PRODUTO) AS COD_PRODUTO, MAX(DESCRICAO) AS DESCRICAO, SUM(VALOR_PROD) AS VALOR_PROD, SUM(VALOR_DANFE) AS VALOR_DANFE, MAX(NATUREZA) AS NATUREZA, MAX(DESC_NAT) AS DESC_NAT FROM (SELECT DISTINCT D1_TIPO AS TIPO, F1_DOC AS DANFE, F1_EMISSAO AS DATA_EMISSAO, F1_DTDIGIT AS DT_LANC_SISTEMA, F1_FORNECE AS FORNECEDOR, F1_LOJA AS LOJA, A2_NOME AS RAZAO, D1_COD AS COD_PRODUTO, B1_DESC AS DESCRICAO, D1_TOTAL AS VALOR_PROD, F1_VALBRUT AS VALOR_DANFE, '' AS NATUREZA, '' AS DESC_NAT FROM SD1010 JOIN SF1010 ON D1_DOC = F1_DOC AND D1_SERIE = F1_SERIE AND D1_FORNECE = F1_FORNECE AND D1_LOJA = F1_LOJA JOIN SB1010 ON B1_FILIAL = ' ' AND B1_COD = D1_COD JOIN SA2010 ON A2_COD = F1_FORNECE AND A2_LOJA = F1_LOJA WHERE D1_DTDIGIT >= '20131201' AND D1_DTDIGIT <= '20131217' AND D1_TIPO = 'N' AND SD1010.D_E_L_E_T_ <> '*' AND SB1010.D_E_L_E_T_ <> '*' AND SF1010.D_E_L_E_T_ <> '*' AND SA2010.D_E_L_E_T_ <> '*' GROUP BY D1_TIPO , F1_DOC , F1_FORNECE , F1_LOJA , A2_NOME , D1_TIPO , D1_COD , B1_DESC , F1_VALBRUT , F1_EMISSAO , F1_DTDIGIT , F1_VALMERC , B1_GRUPO , D1_TOTAL UNION ALL SELECT DISTINCT '', E2_NUM AS DANFE, '', '', E2_FORNECE AS FORNECEDOR, E2_LOJA AS LOJA, '', '', '', '', '', E2_NATUREZ AS NATUREZA, ED_DESCRIC AS DESC_NAT FROM SE2010 INNER JOIN SF1010 ON E2_NUM = F1_DOC AND E2_FORNECE = F1_FORNECE AND E2_LOJA = F1_LOJA INNER JOIN SED010 ON ED_CODIGO = E2_NATUREZ INNER JOIN SD1010 ON D1_DOC = F1_DOC AND D1_SERIE = F1_SERIE AND D1_FORNECE = F1_FORNECE AND D1_FILIAL = F1_FILIAL WHERE E2_EMIS1 >= '20131201' AND E2_EMIS1 <= '20131217' AND SE2010.D_E_L_E_T_ <> '*' AND SED010.D_E_L_E_T_ <> '*' GROUP BY E2_NUM , E2_FORNECE , E2_LOJA , E2_NATUREZ , ED_DESCRIC) AS A INNER JOIN SA2010 ON A.FORNECEDOR = A2_COD AND A.LOJA = A2_LOJA GROUP BY DANFE , FORNECEDOR , LOJA
19/12/2013
Alexandre
Grato
Clique aqui para fazer login e interagir na Comunidade :)