Multiple columns are specified in an aggregated expression containing an outer reference

the following query is giving error.

SELECT job.job,
    (
        SELECT SUM((jrt_sch.setup_ticks / 100) + ((jrt_sch.run_ticks_lbr / 100) * job.qty_released))
        FROM jrt_sch
        WHERE jrt_sch.job = job.job
        ) plnlbr
FROM job
WHERE job.job = 'J000069762'
    AND job.suffix = '0'

I am not able to use job.qty_released in side second select giving following error.

Multiple columns 开发者_开发百科are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

To fix the syntax problem you just need to move the reference to job.qty_released outside the brackets.

SELECT job.job,
    job.qty_released * (
        SELECT SUM((jrt_sch.setup_ticks / 100) + ((jrt_sch.run_ticks_lbr / 100)))
        FROM jrt_sch
        WHERE jrt_sch.job = job.job
        ) plnlbr
FROM job
WHERE job.job = 'J000069762'
    AND job.suffix = '0'

I would tend to write this query as a join though.

it should look something like this:

SELECT job.job, SUM((jrt_sch.setup_ticks / 100) + 
((jrt_sch.run_ticks_lbr / 100) *      job.qty_released)) plnlbr
from job join jrt_sch on jrt_sch.job=job.job
where job.job ='J000069762' and job.suffix='0'
group by job.job