The subqueries for column values used in this case are called correlated subqueries because they reference columns from the main query(ct). You can get the same result w/o using corelated subqueries. It should be also faster

insert into ctAmbiguities
	select distinct ct.ccustno, ct.citemno, ct.cvendno, count(*) as ncntcont.
		SUM( CASE WHEN ct.lusethisone = 0 THEN 1 ELSE 0 as ncnt0,
		SUM( CASE WHEN ct.lusethisone = 1 THEN 1 ELSE 0 as ncnt1,
		SUM( CASE WHEN ct.lusethisone = 2 THEN 1 ELSE 0 as ncnt2,
		SUM( CASE WHEN ct.lusethisone = 3 THEN 1 ELSE 0 as ncnt3
from ctcontitem ct
where cstatus = 'A' or cstatus = 'N'
group by ct.ccustno, ct.citemno, ct.cvendno

>I am trying to understand the syntax of this sql statement but I am confused by it. I think the issues for me are that:
>a) I know that the objective is to take for given customer/item/vendor combination determine the count of records where the 'lusethisone' flag is set to 0, or 1, or 2, or 3.
>b) I really don't understand how the subqueries work here. It seems that the subquery 'references itself??'
>
>I didn't write the query - I am doing maintenance on an existing app. It works so I'm sure it does it's job, but I just don't understand it.
>
>Also, it takes a long time to execute.
>
>Any help appreciated. Thanks
>
>insert into ctAmbiguities
>select distinct ct.ccustno, ct.citemno, ct.cvendno, count(*) as ncntcont
>(select count(*) from ctcontitem where ccustno = ct.ccustno and citemno = ct.citemno and
>cvendno = ct.cvendno and lusethisone = 0 and (cstatus = 'A' or cstatus = 'N')) as ncnt0,
>(select count(*) from ctcontitem where ccustno = ct.ccustno and citemno = ct.citemno and
>cvendno = ct.cvendno and lusethisone = 1 and (cstatus = 'A' or cstatus = 'N')) as ncnt1,
>(select count(*) from ctcontitem where ccustno = ct.ccustno and citemno = ct.citemno and
>cvendno = ct.cvendno and lusethisone = 2 and (cstatus = 'A' or cstatus = 'N')) as ncnt2,
>(select count(*) from ctcontitem where ccustno = ct.ccustno and citemno = ct.citemno and
>cvendno = ct.cvendno and lusethisone = 3 and (cstatus = 'A' or cstatus = 'N')) as ncnt3
>from ctcontitem ct
>where cstatus = 'A' or cstatus = 'N'
>group by ct.ccustno, ct.citemno, ct.cvendno)
>
>
>delete from ctambiguities where (ncnt1=1 and ncnt2=0 and ncnt3=0) or
>(ncntcont = (ncnt0 + ncnt2 + ncnt3)))
>
>
>update ctcontitem set lusethisone = 1
>where ccustno+citemno in (select ccustno+citemno from ctambiguities)