March 23, 2012 at 7:11 am
Is there any way i can make below query more simple ?
There are total 5 queries with 5 temp tables. I need to make it simple using just one query.
SELECT DISTINCT x0_0.keydoc,
x0_0.keyfreesiteaccess,
x0_0.keyfreesiteaccessoverride,
x0_0.keyfreesiteaccesspublishable
INTO #freesiteaccess
FROM objectviews..aisarticle x0_0
WHERE x0_0.keydoc = @KeyDoc
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN x0_0.keyfreesiteaccessoverride
END AS keyfreesiteaccessoverride,
x0_0.keydoc
INTO #freesiteaccessoverride
FROM objectviews.dbo.aisarticle x0_0
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NOT NULL
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishableexclusivefeature,
x0_0.keydoc
INTO #freesiteaccessexclusivefeature
FROM objectviews..aisarticle x0_0
INNER JOIN objectviews..aisarticletype x1_0
ON x0_0.keyarticletype = x1_0.keyarticletype
WHERE x1_0.keyfreesiteaccess = 0
AND x0_0.keyarticlemarketingbadge = 0
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishabledepartments,
x0_0.keydoc
INTO #freesiteaccessdepartments
FROM objectviews.dbo.aisarticle x0_0
INNER JOIN objectviews.dbo.aisarticleindex x1_0
ON x0_0.keydoc = x1_0.keydoc
LEFT JOIN objectviews.dbo.aisarticletype x2_0
ON x0_0.keyarticletype = x2_0.keyarticletype
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NULL
AND ( ( x1_0.keyarticlesubject = 12
AND x1_0.primarysubject = 1
AND x0_0.articleamendmentcount IS NULL )
OR ( x1_0.keyarticlesubject IN ( 502, 503 )
AND x1_0.primarysubject = 1 ) )
AND ( x0_0.keyarticlemarketingbadge IS NULL
OR x0_0.keyarticlemarketingbadge = 9 )
AND ( x2_0.keyarticletype IS NULL
OR x2_0.keyfreesiteaccess = 0 )
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishableinstnfiling,
x0_0.keydoc
INTO #freesiteaccessinstnfilings
FROM objectviews.dbo.aisarticle x0_0
LEFT JOIN objectviews.dbo.aisarticletype x1_0
ON x0_0.keyarticletype = x1_0.keyarticletype
INNER JOIN objectviews.dbo.aisdocsreference x2_0
ON x0_0.keydoc = x2_0.keydoc
INNER JOIN objectviews.dbo.instnfiling x3_0
ON x2_0.keydocreferenced = x3_0.keydoc
INNER JOIN objectviews.dbo.instnfilingtype x4_0
ON x3_0.keyinstnfilingtype = x4_0.keyinstnfilingtype
INNER JOIN objectviews.dbo.docsfilingalias x5_0
ON x4_0.filingalias = x5_0.alias
INNER JOIN objectviews.dbo.documentfile g
ON x3_0.keydoc = g.keydoc
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NULL
AND x5_0.keyinteractiveservicelevel = 0
AND ( ( x4_0.keyinstnfilingtype IN ( 'PR', 'MR', 'ER' )
OR g.keyfilesource = 1 )
AND ( x0_0.keyarticlemarketingbadge IS NULL
OR x0_0.keyarticlemarketingbadge = 9 )
AND ( x1_0.keyarticletype IS NULL
OR x1_0.keyfreesiteaccess = 0 ) )
SELECT x0.keydoc,
CASE
WHEN x3.keyfreesiteaccessoverride IS NOT NULL THEN
x3.keyfreesiteaccessoverride
WHEN x4.keyfreesiteaccesspublishableexclusivefeature = 0 THEN 0
WHEN x1.keyfreesiteaccesspublishabledepartments = 0
OR x2.keyfreesiteaccesspublishableinstnfiling = 0 THEN 0
ELSE x0.keyfreesiteaccesspublishable
END AS keyfreesiteaccesspublishable,
x0.keyfreesiteaccess
FROM #freesiteaccess x0
LEFT JOIN #freesiteaccessdepartments x1
ON x0.keydoc = x1.keydoc
LEFT JOIN #freesiteaccessinstnfilings x2
ON x0.keydoc = x2.keydoc
LEFT JOIN #freesiteaccessoverride x3
ON x0.keydoc = x3.keydoc
LEFT JOIN #freesiteaccessexclusivefeature x4
ON x0.keydoc = x4.keydoc
WHERE x0.keydoc = @KeyDoc
DROP TABLE #freesiteaccess
DROP TABLE #freesiteaccessoverride
DROP TABLE #freesiteaccessexclusivefeature
DROP TABLE #freesiteaccessdepartments
DROP TABLE #freesiteaccessinstnfilings
March 23, 2012 at 7:29 am
well, you can sue CTE's instead of temp tables, but i'm not sure how much simpler that is..it is the same query, different format.
DECLARE @KeyDoc int;
WITH freesiteaccess
AS
(
SELECT DISTINCT x0_0.keydoc,
x0_0.keyfreesiteaccess,
x0_0.keyfreesiteaccessoverride,
x0_0.keyfreesiteaccesspublishable
FROM objectviews..aisarticle x0_0
WHERE x0_0.keydoc = @KeyDoc
)
,freesiteaccessoverride
AS
(
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN x0_0.keyfreesiteaccessoverride
END AS keyfreesiteaccessoverride,
x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NOT NULL
)
,freesiteaccessexclusivefeature
AS
(
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishableexclusivefeature,
x0_0.keydoc
FROM objectviews..aisarticle x0_0
INNER JOIN objectviews..aisarticletype x1_0
ON x0_0.keyarticletype = x1_0.keyarticletype
WHERE x1_0.keyfreesiteaccess = 0
AND x0_0.keyarticlemarketingbadge = 0
)
,freesiteaccessdepartments
AS
(
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishabledepartments,
x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
INNER JOIN objectviews.dbo.aisarticleindex x1_0
ON x0_0.keydoc = x1_0.keydoc
LEFT JOIN objectviews.dbo.aisarticletype x2_0
ON x0_0.keyarticletype = x2_0.keyarticletype
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NULL
AND ( ( x1_0.keyarticlesubject = 12
AND x1_0.primarysubject = 1
AND x0_0.articleamendmentcount IS NULL )
OR ( x1_0.keyarticlesubject IN ( 502, 503 )
AND x1_0.primarysubject = 1 ) )
AND ( x0_0.keyarticlemarketingbadge IS NULL
OR x0_0.keyarticlemarketingbadge = 9 )
AND ( x2_0.keyarticletype IS NULL
OR x2_0.keyfreesiteaccess = 0 )
)
,freesiteaccessinstnfilings
AS
(
SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN 0
END AS keyfreesiteaccesspublishableinstnfiling,
x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
LEFT JOIN objectviews.dbo.aisarticletype x1_0
ON x0_0.keyarticletype = x1_0.keyarticletype
INNER JOIN objectviews.dbo.aisdocsreference x2_0
ON x0_0.keydoc = x2_0.keydoc
INNER JOIN objectviews.dbo.instnfiling x3_0
ON x2_0.keydocreferenced = x3_0.keydoc
INNER JOIN objectviews.dbo.instnfilingtype x4_0
ON x3_0.keyinstnfilingtype = x4_0.keyinstnfilingtype
INNER JOIN objectviews.dbo.docsfilingalias x5_0
ON x4_0.filingalias = x5_0.alias
INNER JOIN objectviews.dbo.documentfile g
ON x3_0.keydoc = g.keydoc
WHERE x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NULL
AND x5_0.keyinteractiveservicelevel = 0
AND ( ( x4_0.keyinstnfilingtype IN ( 'PR', 'MR', 'ER' )
OR g.keyfilesource = 1 )
AND ( x0_0.keyarticlemarketingbadge IS NULL
OR x0_0.keyarticlemarketingbadge = 9 )
AND ( x1_0.keyarticletype IS NULL
OR x1_0.keyfreesiteaccess = 0 ) )
)
--the final query
SELECT x0.keydoc,
CASE
WHEN x3.keyfreesiteaccessoverride IS NOT NULL THEN
x3.keyfreesiteaccessoverride
WHEN x4.keyfreesiteaccesspublishableexclusivefeature = 0 THEN 0
WHEN x1.keyfreesiteaccesspublishabledepartments = 0
OR x2.keyfreesiteaccesspublishableinstnfiling = 0 THEN 0
ELSE x0.keyfreesiteaccesspublishable
END AS keyfreesiteaccesspublishable,
x0.keyfreesiteaccess
FROM reesiteaccess x0
LEFT JOIN freesiteaccessdepartments x1
ON x0.keydoc = x1.keydoc
LEFT JOIN freesiteaccessinstnfilings x2
ON x0.keydoc = x2.keydoc
LEFT JOIN freesiteaccessoverride x3
ON x0.keydoc = x3.keydoc
LEFT JOIN freesiteaccessexclusivefeature x4
ON x0.keydoc = x4.keydoc
WHERE x0.keydoc = @KeyDoc
Lowell
March 23, 2012 at 7:33 am
That's actually one of the same thing. But can we put it into one single query rather then 5 or 6 different query?
March 23, 2012 at 7:34 am
Not really sure what you mean by more simple? I can offer a quick suggestion to make it about a million time more readable. Look at using CamelCase http://en.wikipedia.org/wiki/Pascal_case.
youcanusecamelcasetoamakealongstringofwordscrammedtogetherreadable.
What is easier?
keyfreesiteaccesspublishableexclusivefeature
or
KeyFreeSiteAccessPublishableExclusiveFeature
To make your query more "simple" and get all of this data in a single result set take a look at UNION.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 23, 2012 at 7:45 am
Just so you know, sometimes it is better to break things up like you have then trying to do it all in one query. Trying to do everything in one query isn't always best.
March 23, 2012 at 7:45 am
EasyBoy (3/23/2012)
Is there any way i can make below query more simple ?There are total 5 queries with 5 temp tables. I need to make it simple using just one query....
[/code]
Making it look more simple isn't a good enough reason for changing the query set - unless the changes make it run faster, leave it as it is.
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 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply