query correction SAP Business One HANA

[nikunjmehta2290]

Dear all,

Help me on query correction

SELECT T0."DocEntry",T0."DocNum", T0."CardCode", T0."CardName", T3."GroupName" AS "Categary",T0."Address", T0."DocDate", T1."ItemCode", T1."Dscription",

T1."Quantity",T1."U_UNE_MRP",T1."Price", T5."DistNumber", T5."MnfSerial", T5."LotNumber",

T1."LineTotal" as "value without tax", (T1."Quantity"*T1."U_UNE_MRP") AS "Total MRP Value",

T1."VatSum" AS "TAX",(T1."LineTotal"+T1."VatSum") AS "VALUE WITH TAX", T1."WhsCode",

(CASE WHEN T0."CANCELED"='Y' AND T0."DocStatus"='C' THEN 'CANCELED' WHEN T0."CANCELED"='C' AND T0."DocStatus"='C' THEN 'CANCELED Reverse' WHEN T0."CANCELED"='N' AND T0."DocStatus"='C' THEN 'CLOSE' ELSE 'OPEN' END) AS "INVOICE STATUS"

FROM ORIN T0

INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"

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

INNER JOIN OCRG T3 ON T2."GroupCode"=T3."GroupCode"

left JOIN OITM T4 ON T1."ItemCode" = T4."ItemCode"

left JOIN OBTN T5 ON T4."ItemCode" = T5."ItemCode"

WHERE T0."DocDate" >=[%0] and T0."DocDate" <=[%1]AND T0."CANCELED"='N'

order by T0."DocEntry"

When I add last two table with LEFT join then it's showing multiple lines for number of times. I am try to add one column Batch from OBTN table into the query.

Help me on correction query. / join.

[former_member599040]

Try:

left OUTER join.

You need OUTER, depending on the results you are trying to obtain,

the direction you are joining.

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Hope it helps.

[MD1]

it is resolved or pending