cancel
Showing results for 
Search instead for 
Did you mean: 

Delivery information

Former Member
0 Kudos

Hello,

I need both open and closed orders and information from the following tables . How can I do the correct join between these tables.

SELECT DISTINCT dbo.ODLN.DocNum AS Del_No, convert(char(11),ODLN.[DocDate],110) as CreationDate,dbo.DLN1.ItemCode,DLN1.[Dscription] ,RDR1.[U_Per] as Personalize,

OITM.[U_LegacyItem] as LegacyItem,ORDR.[NumAtCard],CAST(RDR1.Text as nvarchar(max)) as PackageID, ODLN.TrackNo,CAST(ORDR.Comments as nvarchar(Max))as Remarks

FROM

Thanks

Max N

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Max......

Hope this will help you....

SELECT T0.[DocNum] As 'PO No.', T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T3.DocNum As 'GRN No.', T2.Quantity  As 'Total Rcvd', T1.[OpenQty], T1.U_Per as 'Personalize', T4.U_LegacyItem As 'LegacyItem', T1.Text, T3TrackNo, T0.Comments  
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry LEFT Join DLN1 t2 On T1.DocEntry=T2.BaseEntry and T1.LineNum=T2.BaseLine LEFT Join ODLN T3 On T2.DocEntry=T3.DocEntry LEFT Join OITM T4 On T4.ItemCode=T1.ItemCode
Where (T0.Docstatus='[%0]' or '[%0]'='') And (T1.LineStatus='[%1]' or '[%1]'='')
Group By T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T3.DocNum, T2.Quantity, T1.U_Per, T4.U_LegacyItem, T1.Text, T3TrackNo, T0.Comments

Regards,

Rahul

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Max,

Try:

SELECT DISTINCT T0.DocNum AS Del_No, convert(char(11),T0.DocDate,110) as CreationDate,T1.ItemCode,T1.Dscription,T2.U_Per as Personalize,

T3.U_LegacyItem as LegacyItem,ORDR.NumAtCard,CAST(T2.Text as nvarchar(max)) as PackageID, T0.TrackNo,CAST(ORDR.Comments as nvarchar(Max))as Remarks

FROM dbo.ODLN T0

INNER JOIN dbo.DLN1 T1 ON T1.DocEntry=T0.DocEntry

LEFT JOIN dbo.RDR1 T2 ON T2.TrgetEntry=T0.DocEntry AND T2.LineNum

=T1.BaseLine

INNER JOIN dbo.OITM T3 ON T3.ItemCode=T1.ItemCode

Thanks,

Gordon