March 4, 2014 at 6:12 am
hi All,
i have a query like this.
i want to rewrite as single query use WHERE EXISTS and NOT EXISTS .
CREATE VIEW [dbo].[job_refreshCompanyCountExtended_vw]
AS
SELECT TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
max(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,
max(CASE WHEN symbolTypeid = 6 and primaryFlag = 1 THEN symbolValue ELSE NULL END) AS dunsNumber
FROM dbo.Symbol_tbl s (nolock)
INNER JOIN dbo.company_tbl (nolock)
ON dbo.company_tbl.companyId = s.objectId
AND (dbo.company_tbl.userCompanyId = s.userCompanyId OR dbo.company_tbl.userCompanyId=0)
where exists (select * from Symbol_tbl _s (nolock)
where _s.objectId = s.objectId
and _s.symbolTypeId = 6
and _s.primaryFlag = 1)
group by s.objectid
order by s.objectid
UNION
SELECT TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
max(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,
max(CASE WHEN symbolTypeid = 6 THEN symbolValue ELSE NULL END) AS dunsNumber
FROM dbo.Symbol_tbl s (nolock)
INNER JOIN dbo.company_tbl (nolock)
ON dbo.company_tbl.companyId = s.objectId
AND (dbo.company_tbl.userCompanyId = s.userCompanyId OR dbo.company_tbl.userCompanyId=0)
where not exists (select * from Symbol_tbl _s (nolock)
where _s.objectId = s.objectId
and _s.symbolTypeId = 6
and _s.primaryFlag = 1)
group by s.objectid
order by s.objectid
;
March 4, 2014 at 6:31 am
SELECT --TOP 100 PERCENT
objectid AS companyid,
-- max(CASE WHEN symbolTypeid = 7 THEN CONVERT(int, symbolValue) ELSE NULL END) AS mergentId,
MAX(CASE WHEN symbolTypeid = 5 THEN symbolValue ELSE NULL END) AS marketGuideRepNo,
MAX(CASE WHEN symbolTypeid = 6 and primaryFlag = 1 THEN symbolValue ELSE NULL END) AS dunsNumber,
x.MyBool
FROM dbo.Symbol_tbl s (nolock)
INNER JOIN dbo.company_tbl c (nolock)
ON c.companyId = s.objectId
AND (c.userCompanyId = s.userCompanyId OR c.userCompanyId=0)
CROSS APPLY (
SELECT MyBool = CASE WHEN EXISTS (
SELECT 1 FROM dbo.Symbol_tbl _s (nolock)
WHERE _s.objectId = s.objectId
AND _s.symbolTypeId = 6
AND _s.primaryFlag = 1) THEN 1 ELSE 0 END
) x
GROUP BY s.objectid, x.MyBool
--ORDER BY s.objectid, x.MyBool
-- don't use TOP 100 PERCENT ...ORDER BY in a view definition.
-- In recent versions of SQL Server it's ignored (try figuring that out when you upgrade), in later versions it may raise an error
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply