cancel
Showing results for 
Search instead for 
Did you mean: 

Joining SQL tables.

Former Member
0 Kudos
305

Hi All,

I need this portion of SQL urgently. How do i link ORDR and OPKL tables together? i want to call our my SO with the status release (R). But the release date would be from OPKL table <--'PickDate' . How do i join this? Basically i want to join the PickDate with my SO released.

Appreciate your advice.

Thanks,

Harith

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

try this one:


SELECT T0.[PickDate], T1.[Pick], T1.[PickRmrk], 
T1.[PickStatus], T1.[DocNum], T1.[DocDate], 
T1.[CardCode] FROM OPKL T0 inner join 
ORDR T1 on t1.ownercode = t0.ownercode 
WHERE T0.[PickDate]  between '[%0]' and '[%1]'

Rajh

Former Member
0 Kudos

Hi sir,

Practically the query return me nothin. No result. Any ideas?

Thanks

Harith

former_member583013
Active Contributor
0 Kudos

Harith,

OPKL and not linked directly with ORDR. It is only PKL1 (Pick List - Rows) which is linked through the OrderEntry field.

Therefore the link is

SELECT T2.AbsEntry AS 'Picklist Entry', T1.OrderEntry AS 'Order Entry', T0.DocNum AS 'Document Number', T0.DocDate AS 'Posting Date'

FROM [dbo\].[ORDR\] T0 INNER JOIN [dbo\].[PKL1\] T1 ON T0.DocEntry = T1.OrderEntry

INNER JOIN [dbo\].[OPKL\] T2 ON T2.AbsEntry = T1.AbsEntry

Best wishes

Suda

Former Member
0 Kudos

Hi suda,

How do i call out the Pickdate from here? The posting date, you are referring to the SO date right? But i would want it to have a variables where there's a selection criteria of the 'PickDate' fields.

Thanks,

Harith

former_member583013
Active Contributor
0 Kudos

SELECT T2.AbsEntry AS 'Picklist Entry', T1.OrderEntry AS 'Order Entry', T0.DocNum AS 'Document Number', T0.DocDate AS 'Posting Date'

FROM [dbo\].[ORDR\] T0 INNER JOIN [dbo\].[PKL1\] T1 ON T0.DocEntry = T1.OrderEntry

INNER JOIN [dbo\].[OPKL\] T2 ON T2.AbsEntry = T1.AbsEntry

WHERE T2.PickDate = '[%0\]'

OR

SELECT T2.AbsEntry AS 'Picklist Entry', T1.OrderEntry AS 'Order Entry', T0.DocNum AS 'Document Number', T0.DocDate AS 'Posting Date'

FROM [dbo\].[ORDR\] T0 INNER JOIN [dbo\].[PKL1\] T1 ON T0.DocEntry = T1.OrderEntry

INNER JOIN [dbo\].[OPKL\] T2 ON T2.AbsEntry = T1.AbsEntry

WHERE T2.PickDate BETWEEN '[%0\]' AND '[%1\]'

Former Member
0 Kudos

Hi suda,

Thank you for the information. I somehow use this portion of query you created & paste it into my query. But, unfortunately the same Sales order number appear so many times, which in result, my quantity is higher than it shud be.Why is this so?

appreciate your advice.

Thanks,

Harith

former_member583013
Active Contributor
0 Kudos

Please use SELECT DISTINCT ........

Add the DISTINCT after the SELECT

Former Member
0 Kudos

hi suda,

Thank for info. But it doesnt seems to be my output. How do you add the item code & item description in this query?

SELECT T2.AbsEntry AS 'Picklist Entry', T0.DocNum AS 'SO No.', T2.PickDate as 'PickDate', T0.DocDate AS 'SO Date', T0.CardCode as 'Customer Code', T3.SlpName, T4.PymntGroup, T0.CardName as 'Customer Name', T0.Address, T0.Address2 FROM ORDR T0 INNER JOIN PKL1 T1 ON T0.DocEntry = T1.OrderEntry INNER JOIN OPKL T2 ON T2.AbsEntry = T1.AbsEntry INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode INNER JOIN OCTG T4 ON T0.GroupNum = T4.GroupNum WHERE T2.PickDate BETWEEN '[%0]' AND '[%1]'

Thanks.

Harith

former_member583013
Active Contributor
0 Kudos

Harith,

I gave you the concept but I would not know what all you need as part of the result.

I have added the ItemCode and Name

SELECT T5.ItemCode, T5.Dscription, T2.AbsEntry AS 'Picklist Entry', T0.DocNum AS 'SO No.', T2.PickDate as 'PickDate', T0.DocDate AS 'SO Date', T0.CardCode as 'Customer Code', T3.SlpName, T4.PymntGroup, T0.CardName as 'Customer Name', T0.Address, T0.Address2 FROM RDR1 T5 INNER JOIN ORDR T0 ON T5.DocEntry = T0.DocEntry INNER JOIN PKL1 T1 ON T0.DocEntry = T1.OrderEntry INNER JOIN OPKL T2 ON T2.AbsEntry = T1.AbsEntry INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode INNER JOIN OCTG T4 ON T0.GroupNum = T4.GroupNum WHERE T5.LineNum = T1.OrderLine AND T2.PickDate BETWEEN '[%0\]' AND '[%1\]'

Answers (0)