Related to Query

[Former Member]

I was Create a query for marketing for marketing report in which following fields are coverd Itemcode Itemdesc Qty Sales Value Taxestotal Total CustCode & CustName but in this Query duplication of Itemcode is Occured so How can i Removed this DUPLICATION following is my designed QUERY please suggest me what i do for that

SELECT T0.[ItemCode], T0.[Dscription], T0.[Quantity], (T0.[LineTotal]-T0.[VatSum]) AS "Sales Value", T0.[VatSum], T0.[LineTotal], T1.[CardCode], T1.[CardName] FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND T0.[ItemCode] =[%2]

[Former Member · Accepted solution]

Hi......

Welcome to SAP Forum.....

try this.......

SELECT Distinct T0.ItemCode, T0.Dscription, T0.Quantity, (T0.LineTotal-T0.VatSum) AS "Sales Value", 
T0.VatSum, T0.LineTotal, T1.CardCode, T1.CardName FROM INV1 T0 INNER JOIN OINV T1 
ON T0.DocEntry = T1.DocEntry WHERE (T0.DocDate >='[%0]' AND T0.DocDate <='[%1]') 
AND T0.ItemCode ='[%2]'

Regards,

Rahul

[Former Member · Accepted solution]

Thanks for replying me Rahul

But this is not worked

[Former Member]

Hi rakssap,

Considering your query runs for a single item but also has an invoice date range, it is going to return multiple lines if that item exists on more than one invoice line within the dates specified. The duplication is correct in relation to the data and the structure of your query.

It does not make sense to use DISTINCT (as in the other queries listed above) in this case because more than likely the other columns will have different values and so the same item will still be returned multiple times. For example, you are listing the DocNum column - that will have different values if more than one invoice has that item within the date range. DISTINCT is pointless in this case.

Maybe rethink what you are trying to show in relation to the structure of your query. Do you need to use GROUP BY and SUM the Quantity/Value type fields for example?

Regards,

Andrew.

[Former Member]

Hi Mr. Andrew,

I think you are right,

[Former Member]

Hi Rakssap

This Query is Working Perfectly

SELECT Distinct T0.ItemCode, T0.Dscription, T0.Quantity, (T0.LineTotal-T0.VatSum) AS "Sales Value", T0.VatSum, T0.LineTotal, T1.CardCode, T1.CardName FROM INV1 T0 INNER JOIN OINV T1 ON T0.DocEntry = T1.DocEntry WHERE (T0.DocDate >='[%0]' AND T0.DocDate <='[%1 ]') AND T0.ItemCode ='[%2]'

Thanks

Shafi

[Former Member]

Hi,

Try this to make sure no omission:

SELECT Distinct T0.ItemCode, T0.Dscription, T0.Quantity, (T0.LineTotal-T0.VatSum) AS "Sales Value", 
T0.VatSum, T0.LineTotal, T1.CardCode, T1.CardName, T1.DocNum,T0.LineNum FROM INV1 T0 INNER JOIN OINV T1 
ON T0.DocEntry = T1.DocEntry WHERE (T0.DocDate >='[%0]' AND T0.DocDate <='[%1]') AND T0.ItemCode ='[%2]'

Thanks,

Gordon

[Former Member]

Thanks for replying me Gordon

But this is not worked

[Former Member]

Hi......

This query must work even though it shows wrong result because nothing is wrong syntax wise......

Is that executing atleast?

Or giving error?

Regards,

Rahul

[Former Member]

Try:

WHERE T0.DocDate >=[%0\] AND T0.DocDate <= [%1\] AND T0.ItemCode ='[%2\]' instead.