April 24, 2003 at 10:58 am
This is an inventory system that allows setting low threshold inventory levels for system alerts. The query is supposed to select all products where the quantity is less than or equal to the threshold limit. The problem I'm having is the last part where the query should only select products where there an order hasn't been placed. Since products will be ordered many times, the query look for the most recent order for the product and checks to see if it has been received or returned to the vendor. If an order was placed but hasn't been received and not returned, then even though the quantity is less than the threshold, it shouldn't be in the query results. Here is my query that isn't working (I think I'm not using the "not exists" properly):
Select
ProductID,
InternalID,
ProductName
from Products P
where P.quantity <= P.threshold AND P.threshold != 0 /*zero means ignore*/
and not exists
/*Don't illustrate a low threshold product after it has been ordered. after and order has been received, it should be ignored for purposes of low threshold unless it was returned*/
(
Select
max(PO.OrderDate) /*most recent order*/
from ProductOrders PO
Where PO.fkProductID = P.ProductID and
(PO.ReceivedDate is Null and PO.returneddate is null)
)
Any ideas will be appreciated.
Thanks,
Doug
April 24, 2003 at 11:27 am
I think instead of doing a max() function in your "not exists" function you need to use a top 1 possibly. I think the Max is always returning 1 row but the value is null no matter what is in your where clause.
Darren
Darren
Darren
April 24, 2003 at 1:40 pm
That did it, thank you so much!
Doug
quote:
I think instead of doing a max() function in your "not exists" function you need to use a top 1 possibly. I think the Max is always returning 1 row but the value is null no matter what is in your where clause.Darren
Darren
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply