Skip to Content
0
Apr 19, 2016 at 12:45 AM

Attempting to join two queries!

33 Views

Hi,

I have this query:

SELECT T0.[ItemCode], T0.[ItemName], T0.[U_Brand], T0.[CodeBars], T1.[Price],T0.[U_MinOrderQty], T0.[LastPurPrc], T0.[OnHand] as [On Hand], T0.[IsCommited] as [Commited], T0.[OnOrder], (T0.[OnHand]+T0.[OnOrder]) - T0.[IsCommited] as [Available], T0.[FrozenFor] as [INACTIVE], T0.[U_Category] as [Category], T0.[QryGroup1] as [DISCONT], T0.[QryGroup8] as [NOT FOR REORDER], T0.[QryGroup12] as [ADDED TO SAP], T0.[QryGroup20] as [TBA]

FROM OITM T0 INNER JOIN ITM1 T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.[FrozenFor] = 'N' AND T0.[PrchseItem] = 'Y' AND T0.[SellItem] = 'Y' AND T0.[SellItem] = 'Y' AND T1.[PriceList] = 1

GROUP BY T0.[ItemCode], T0.[ItemName], T0.[U_Brand], T0.[CodeBars], T1.[Price],T0.[U_MinOrderQty], T0.[LastPurPrc], T0.[OnHand], T0.[IsCommited],T0.[OnOrder], T0.[FrozenFor], T0.[U_Category], T0.[QryGroup1], T0.[QryGroup8], T0.[QryGroup12], T0.[QryGroup20]

ORDER BY T0.[ItemCode], T0.[ItemName], T0.[U_Brand], T0.[CodeBars] , T1.[Price], T0.[LastPurPrc], T0.[OnHand], T0.[IsCommited],T0.[OnOrder], T0.[FrozenFor], T0.[U_Category], T0.[QryGroup1], T0.[QryGroup8], T0.[QryGroup12], T0.[QryGroup20]

And I need to add in the a new column - the earliest in stock date for an item - so the DocDueDate field on the OPOR table.

I have this query:

SELECT T2.[DocNum], T1.[ItemCode], T1.[Dscription], min(T2.[DocDueDate]) FROM OITM T0 INNER JOIN POR1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OPOR T2 ON T1.DocEntry = T2.DocEntry WHERE T1.[ItemCode] = 'AP4000' and T2.[DocStatus] = 'O' GROUP BY T2.[DocNum], T1.[ItemCode], T1.[Dscription], T2.[DocDueDate] ORDER BY T2.[DocNum], T1.[ItemCode], T1.[Dscription], T2.[DocDueDate]

The issue that I am running into with this one is that I am trying to get the earliest in stock date.

Some items we have in stock we have multiple PO's for - I just want to return one value - the earliest DocDueDate.

First and foremost - is using the Min() function correct - if so, why doesn't it work on the above?

Secondly - how would I go about implementing this into my initial query?

Regards

Rick