topic Re: Query generaton in Application Development and Automation Discussions

Query generaton

former_member765243 — Sun, 05 Sep 2021 11:20:36 GMT

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.

Re: Query generaton

former_member688242 — Wed, 08 Sep 2021 10:30:22 GMT

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

Re: Query generaton

former_member765243 — Mon, 13 Sep 2021 10:49:41 GMT

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

Re: Query generaton

former_member688242 — Mon, 13 Sep 2021 12:35:03 GMT

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