Skip to Content

Problem with Pick and Pack Query

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Mar 22, 2017 at 12:44 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2017 at 11:14 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 22, 2017 at 11:51 AM

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

    Add comment
    10|10000 characters needed 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