i got some trouble with a SQL query for pick lists. I am going to create a report, that displays from the pick & pack manager the hole BP and order informations. Therefor i wrote this query, which works properly :
SELECT T3.* FROM PKL1 T0 INNER JOIN RDR1 T1 ON T0.OrderEntry=T1.DocEntry AND
T0.OrderLine=T1.LineNum INNER JOIN OCRD T3 ON T3.CardCode=T1.BaseCard WHERE
T0.AbsEntry = [%AbsEntry]
My problem is, the bp and order query creates a new line for each row of the pick list. So the bp repeats as often as the order is long. I tried to use a DISTINCT statement, but it does not work.
Any ideas how to solve it ?
Thanks in advance
please try the following query:
SELECT T3.CardCode, T3.CardName, min(T3.CardType) as Cardtype, min(T3.Address) as Address
/* all columns you need */
FROM PKL1 T0 INNER JOIN RDR1 T1 ON T0.OrderEntry=T1.DocEntry AND
T0.OrderLine=T1.LineNum INNER JOIN OCRD T3 ON T3.CardCode=T1.BaseCard
GROUP BY T3.CardCode, T3.CardName
WHERE T0.AbsEntry = [%AbsEntry]
Please Note when you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause.
hope it helps,
SAP Business One Forum Team