Needing help with a query

  • 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

  • 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

  • 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