Query generaton

[former_member765243]

I need a query to get sales-return of all customers. For this I use the following query that shows no result

SELECT T0.[CardCode], sum(T0.[DocTotal]-T1.[DocTotal]) FROM OINV T0 INNER JOIN ORIN T1 ON T0.[DocEntry] = T1.[AnnInvDecR] WHERE T0.[CANCELED] ='n' AND T1.[CANCELED] = 'n' GROUP BY T0.[CardCode]

Then I change it to the following, it shows wrong result

SELECT T0.[CardCode], sum(T0.[DocTotal]-T1.[DocTotal]) FROM OINV T0 INNER JOIN ORIN T1 ON T0.[CardCode] = T1.[CardCode] WHERE T0.[CANCELED] ='n' AND T1.[CANCELED] = 'n' GROUP BY T0.[CardCode]

Please suggest the correction.

[former_member688242]

please share structure of table ORIN and OINV and foreign key relationship between these table Also pleasw specify sales-return data type.

relationship between table(ORIN and OINV) is one to one or realationship between table(ORIN and OINV) is one to many

[former_member765243]

Its all about SAP B1. For "Sales return" we are using "A/R Credit memo".

[former_member688242]

I don’t have access to bi system.please share details mentioned above