Query [BatchNum] duplicates item

41 Views

Hi Everyone, i create a query, but i have errors...

----------NF Saida------------

SELECT distinct T0.[ItemCode] as 'Cod.Prod.', T0.[Dscription] as 'Produto Basico','' as 'Produto','' as 'Cod.Cultura' ,T0.[U_U_CULTURA] ,T1.[Serial] as 'NF', T1.[DocDate] as 'Data','' as 'Nº Mes' ,'' as 'Mes', T1.[CardCode] as 'Cod.Cliente', T1.[CardName] as 'Cliente' , T5.[CityS] as 'Localidade', '' as 'Macro','' as 'Revenda', T6.[SlpName] as 'Regional', T5.[State] as 'Estado 1', '' as 'Estado 2', '' as 'Região', T0.[Quantity] as 'Caixas', T0.[Price] as 'Preço da Caixa','' as 'L ou Kg', '' as 'Preço Unitário' ,T0.[LineTotal] as 'TT', T4.[PymntGroup] as 'Prazo em Dias','' as 'Prazo M. Dias','' as 'Prazo Médio','' as 'Juros a.m.','' as 'Index','' as 'ICMS','' as 'Transp.', T3.[BatchNum] ,T1.[Serial] as 'NF','' as 'Valor frete R$', '' as '% Frete','' as 'Retorno' ,'' as 'Entrega', '' as 'Tampas / Pacotes', '' as 'Frascos',T5.[Carrier], T1.[U_Justfi], T1.[ObjType], T3.[BatchNum]

FROM INV1 T0

INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OCRD T2 ON T0.BaseCard = T2.CardCode

INNER JOIN IBT1 T3 ON T2.CardCode = T3.CardCode AND

T1.DocDate = T3.DOCDATE AND T0.ITEMCODE = T3.ITEMCODE AND T0.Quantity = T3.Quantity

INNER JOIN OCTG T4 ON T1.GroupNum = T4.GroupNum

INNER JOIN INV12 T5 ON T1.[DocEntry] = T5.[DocEntry]

INNER JOIN OSLP T6 ON T0.[SlpCode] = T6.[SlpCode]

WHERE T1.[DocType] = 'I' AND T1.[Serial] = '8901' and T1.[CANCELED] <> 'yes 'and T1.[SeqCode] <> '1'

I created a query , that query bring a lot of data , but I need [BatchNum] field , and when I include that field in query duplicates some items that are not actually duplicates , eg :

this query it returns:

# Cod.Prod. Produto Basico NF BatchNum 1 1610 TOCHA Cx. 4x5L 8901 120809-09-10000 2 1610 TOCHA Cx. 4x5L 8901 190609-09-10000 3 1610 TOCHA Cx. 4x5L 8901 220609-09-10000 4 210 DICARZOL 500 8901 038-13-1000

But the correct result is :

# Cod.Prod. Produto Basico NF 1 1610 TOCHA Cx. 4x5L 8901 2 210 DICARZOL 500 8901

when I erase the T3.[BachNum] , the result appears correctly, maybe I'm linking queries the wrong way , please someone help me solve this problem ?