CODEWITHSUNDEEP
sqlsubqueryvisual-foxpro
Rob
Rob

Reputation: 59

SQL Math Operation In Correlated Subquery

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?

Upvotes: 0

Views: 198

Answers (1)

DouadyRabbit
DouadyRabbit

Reputation: 130

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

Upvotes: 1

Related Questions