Multiple columns are specified in an aggregate expression containing an external link.

Question

The following query gives an 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 cannot use job.qty_released in the lateral second selection, giving the following error.

Multiple columns are specified in an aggregate expression containing an external link. If the aggregated expression contains an external link, then this external link must be the only column referenced by the expression.

Answer 1

To fix the syntax problem, you just need to move the link 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 like to write this query as join , though.

Answer 2

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