Batch Query Duplicate records

[kedalenechong]

Hi All

Please help correct my Batch query below with Duplicate records.

SELECT
T0."DocEntry" AS "GRN DocEntry",
T0."DocNum" AS "GRN Number",
T0."DocDate" AS "Document Date",
T0."DocCur" AS "Currency",
T1."LineNum" AS "Line Number",
T1."ItemCode" AS "Item Code",
T3."ItemName" AS "Item Name",
T1."Quantity" AS "Quantity Received",
T1."Price" AS "Price",
T2."DistNumber" AS "Batch Number",
LEFT(T2."DistNumber", 6) AS "Manufacturing Date",
T2."ExpDate" AS "Expiry Date",
T1."WhsCode" AS "Warehouse Code",
T4."Quantity" AS "Available Quantity"
FROM
OIGN T0
INNER JOIN
IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN
OBTN T2 ON T1."ItemCode" = T2."ItemCode"
LEFT JOIN
OITM T3 ON T1."ItemCode" = T3."ItemCode"
LEFT JOIN
OIBT T4 ON T1."ItemCode" = T4."ItemCode" AND T2."DistNumber" = T4."BatchNum" AND T1."WhsCode" = T4."WhsCode"
WHERE
T0."DocDate" >= '[%0]' AND T0."DocDate" <= '[%1]'

Kedalene Chong

[anand_singh · Accepted solution]

Dear Mr. Chong,

Try Below Query:-

SELECT
T0."DocEntry" AS "GRN DocEntry",
T0."DocNum" AS "GRN Number",
T0."DocDate" AS "Document Date",
T0."DocCur" AS "Currency",
T1."LineNum" AS "Line Number",
T1."ItemCode" AS "Item Code",
T3."ItemName" AS "Item Name",
T1."Quantity" AS "Quantity Received",
T1."Price" AS "Price",
T2."BatchNum" AS "Batch Number",
LEFT(T4."PrdDate", 6) AS "Manufacturing Date",
T4."ExpDate" AS "Expiry Date",
T1."WhsCode" AS "Warehouse Code",
T4."Quantity" AS "Available Quantity"
FROM
OIGN T0
INNER JOIN IGN1 T1 ON T0."DocEntry" = T1."DocEntry"
LEFT JOIN IBT1 T2 ON T1."DocEntry" =T2."BaseEntry" and T1."LineNum" =T2."BaseLinNum" and T1."ItemCode" = T2."ItemCode"
LEFT JOIN OITM T3 ON T1."ItemCode" = T3."ItemCode"
LEFT JOIN OIBT T4 ON T1."ItemCode" = T4."ItemCode" AND T2."BatchNum" = T4."BatchNum" AND T1."WhsCode" = T4."WhsCode" 
WHERE
T0."DocDate" >= '[%0]' AND T0."DocDate" <= '[%1]'

Regards,

Anand

[LoHa]

Hi Kadlene,

it make no sense to join to OBTN by ItemCode (only) you don't need all batches on this point.

When you join on the OIBT you have to connect it with the IGN1 by using BaseLine,BaseEntry,Basetype and direction
e.g.
OIBT.BaseLine = IGN1.LineNum 

regards Lothar