General information
Forum:
Microsoft SQL Server
Title:
Need query syntax help
Environment versions
SQL Server:
SQL Server 2000
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)
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only