cancel
Showing results for 
Search instead for 
Did you mean: 

Query problems for pick lists

Former Member
0 Kudos

Hi Community,

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

Steffen

Accepted Solutions (1)

Accepted Solutions (1)

ladislav_meszaros
Contributor
0 Kudos

Hi Steffen,

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,

Regards,

Ladislav

SAP Business One Forum Team

Answers (0)