Limit Results with a sub query. RRS feed

  • Question

  • User1215529056 posted

    I have production hours in a list of tables to show how much time is required to do a job on the cutter.

    See query below:

    OrderValue.ProductionMinutes / 60 as hours
    FROM OrderValue
    INNER JOIN OrderProcess ON OrderValue.JobNumber = OrderProcess.JobNumber
    AND OrderValue.ComponentNumber = OrderProcess.ComponentNumber
    AND OrderValue.GroupNo = OrderProcess.GroupNo
    AND OrderValue.ItemNumber = OrderProcess.ItemNumber
    AND OrderValue.PartNumber = OrderProcess.PartNumber
    INNER JOIN OrderQtyTable ON OrderValue.JobNumber = OrderQtyTable.JobNumber
    AND OrderValue.ComponentNumber = OrderQtyTable.ComponentNumber
    AND OrderValue.QuantityLineNo = OrderQtyTable.QuantityLineNo
    LEFT JOIN Process ON OrderProcess.ProcessCode = Process.ProcessCode
    INNER JOIN OrderComponent ON OrderValue.JobNumber = OrderComponent.JobNumber
    AND OrderValue.ComponentNumber = OrderComponent.ComponentNumber
    AND OrderValue.QuantityLineNo = OrderComponent.QtyOrdIndex
    WHERE OrderProcess.JobNumber ='23385'
    AND OrderProcess.PartNumber = 0
    AND OrderProcess.DontPrintOnTicket = 0
    AND Process.CostCenterCode = 5000

    What I want to do is to limit the results based on what was done in production.

    If the cutter has completed component 1 and still has to complete component 2 I want component 2 hours to be returned

    I added this in the where clause but it does not like it and I can't seem to find out why and fix it.

    It underlines in red Select

    AND (Select

    from Production
    join ProductionJobNumber on Production.code=ProductionJobNumber.ProductionCode
    join Process pr on Production.ProcessCode=Pr.ProcessCode
    where OrderProcess.JobNumber =ProductionJobNumber.jobnumber
    And Production.CompletionCode<>'30'
    AND Pr.CostCenterCode=5000)

    Thursday, July 19, 2018 7:00 PM

All replies