Hi Sergey,

Sorry for the delayed reply.. have been quite busy.

I thought this was in fact a correlated subquery. Most of the examples of these have been where the correlated subquery part is in the where clause, not in the select list, but apparently it can be done in the select list as well.

I am trying though to understand the order of execution, i.e. my understanding is that for every row returned by the main query the correlated subquery executes once? Is that correct?

Thanks,
David



>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)