Wsrtjtyk

I am working with three tables, basically, one is a bill of materials, one contains part inventory, and the last one contains work orders or jobs. I am trying to find out if it is possible to have a correlated subquery that can perform a math operation using a value from the outer query. Here's an example of what I'm trying to do:

SELECT A.work_order,A.assembly,A.job_quantity,

    (SELECT COUNT(X.part_number)

    FROM bom X

    WHERE X.assembly = A.assembly

    AND (X.quantity_required * A.job_quantity) >= (SELECT Y.quantity_available FROM inventory Y WHERE 

Y.part_number = X.part_number)) AS negatives

FROM work_orders A

ORDER BY A.assembly ASC

I am attempting to find out, for a given work order, if there are parts that we do not have enough of to build the assembly. I'm currently getting an "Error correlating fields" error. Is it possible to do this kind of operation in a single query?

Try moving the subquery to a join, something like this:

   SELECT a.work_order, a.assembly, a.job_quantity, n.negatives

   FROM work_orders a JOIN (SELECT x.part_number, COUNT(x.part_number) as negatives

                     FROM bom x JOIN work_orders b

                     ON x.assembly = b.assembly

                     WHERE (x.quantity_required * b.job_quantity) >= (SELECT y.quantity_available 

                                                                       FROM inventory y WHERE 

                                                                       y.part_number = x.part_number)

                    GROUP BY x.part_number) n

                    ON a.part_number = n.part_number

                    ORDER BY a.assembly ASC

Or create a temporary cursor with the subquery and then use it to join the main table.
Hope this helps.

Luis