Skip to Content
0

Problem with Pick and Pack Query

Mar 22, 2017 at 10:46 AM

73

avatar image

Hi guys - I am trying to write, what I thought would be a simple query, in order to determine who picked which order and on what date. So I wrote the below query, however it displays ALL information as being the same Abs.Entry and the same date - I would just want to select the picking info for the particular day I need - what have I done wrong here or is my over-simplified query just not going to work?

SELECT T1.AbsEntry
, T0.DocNum AS 'Sales Order Number'
, T1.PickDate
, T1.Name AS 'Picker'
, T0.CardCode AS 'Customer/Supplier No.'
, T0.CardName AS 'Customer/Supplier Name'
, T1.Remarks AS 'Remarks'

FROM OPKL T1 , ORDR T0

WHERE T1.PickDate = [%0]

Any help you can provide will be greatly appreciated.

Regards

Roy

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Markus Schäfer Mar 22, 2017 at 12:44 PM
0

Hy Roy,

please try this:

SELECT T2.[DocNum], T0.[PickDate], T0.[Name], T2.[CardCode], T2.[CardName], T0.[Remarks]
FROM OPKL T0
INNER JOIN PKL1 T1 ON T0.[AbsEntry] = T1.[AbsEntry]
INNER JOIN ORDR T2 on T1.OrderEntry = T2.DocEntry
Where T0.PickDate = '[%0]'

The DocNum from ORDR is the table PKL1 as DocEntry - it is not the DocNum

hope that helps...

Markus

Show 3 Share
10 |10000 characters needed characters left characters exceeded

Suberb Markus, that works great and thank you for the heads up regarding my mistake :)

Warmest regards

Roy Bright

0

One last thing Markus - I want to Group them together by the DocNum but because of the REMARKS column, I get the following error:

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

If I remove the remarks, the group by wroks great - is there a way around this? I understand why the GROUP BY will break the query with regards to the Remarks column but cant figure out how to fix it

0

Hello, sorry for my delay...

please send your query.

0
Balaji Selvaraj Mar 22, 2017 at 11:14 AM
0

Hi Roy Bright,

Try this

SELECT T0.AbsEntry , T2.DocNum AS 'Sales Order Number' , T0.PickDate ,T0.Name AS 'Picker' , T2.CardCode AS 'Customer/Supplier No.' , T2.CardName AS 'Customer/Supplier Name' , T0.Remarks AS 'Remarks' FROM OPKL T0 inner join PKL1 T1 on T0.[AbsEntry] = T1.[AbsEntry] inner join ORDR T2 on T1.[OrderEntry] = T2.DocNum

Regards:

Balaji.S

Share
10 |10000 characters needed characters left characters exceeded
Roy Bright Mar 22, 2017 at 11:51 AM
0

Unfortunately Balaji, that now returns no information at all, which is strange as your query looks solid - any ideas my friend?

Show 1 Share
10 |10000 characters needed characters left characters exceeded

SELECT T0.AbsEntry , T2.DocNum AS 'Sales Order Number' , T0.PickDate ,T0.Name AS 'Picker' , T2.CardCode AS 'Customer/Supplier No.' , T2.CardName AS 'Customer/Supplier Name' , T0.Remarks AS 'Remarks' FROM OPKL T0 inner join PKL1 T1 on T0.[AbsEntry] = T1.[AbsEntry] inner join ORDR T2 on T1.[OrderEntry] = T2.DocEntry

0