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)