March 8, 2012 at 11:43 pm
I have 5 different temp table query and would like to combine into single query without using any temp table.
Can anyone please help, it's urgent.
Please find the attachment for the query.
March 8, 2012 at 11:50 pm
By combine you mean combine them as records or columns?
If records you can use UNION ALL. For union all you should have same amount/type/name columns across all select statements. If a table doesn't have a field which previous table has, then return empty-valued/0 column with the exact column name of previous table. '' as columnname / 0 as columnname
If combine by column, then you have to join the tables with necessary join condition.
March 8, 2012 at 11:54 pm
I have to combine them using column and join condition.
It's little bit difficult, that's the reason i have attached query.
March 9, 2012 at 12:04 am
I have modify the query but not getting desire result.
Can anyone have a look both query and let me know if i am missing anything?
DECLARE @keydoc INT
SET @keydoc =
SELECT x0_0.keydoc,
CASE
WHEN ( x0_0.keydoc = @KeyDoc
AND x0_0.keyfreesiteaccessoverride IS NOT NULL ) THEN
x0_0.keyfreesiteaccessoverride
WHEN x0_0.keydoc = @KeyDoc THEN 0
ELSE x0_0.keyfreesiteaccesspublishable
END AS keyfreesiteaccesspublishable,
x0_0.keyfreesiteaccess
FROM objectviews..aisarticle x0_0
INNER JOIN objectviews..aisarticletype x1_0
ON x0_0.keyarticletype = x1_0.keyarticletype
AND x0_0.keyfreesiteaccessoverride IS NOT NULL
AND x1_0.keyfreesiteaccess = 0
AND x0_0.keyarticlemarketingbadge = 0
INNER JOIN objectviews.dbo.aisarticleindex x2_0
ON x0_0.keydoc = x2_0.keydoc
LEFT JOIN objectviews..aisarticletype x7_0
ON x0_0.keyarticletype = x7_0.keyarticletype
AND x0_0.keyfreesiteaccessoverride IS NULL
AND ( ( x2_0.keyarticlesubject = 12
AND x2_0.primarysubject = 1
AND x0_0.articleamendmentcount IS NULL )
OR ( x2_0.keyarticlesubject IN ( 502, 503 )
AND x2_0.primarysubject = 1 ) )
AND ( x0_0.keyarticlemarketingbadge IS NULL
OR x0_0.keyarticlemarketingbadge = 9 )
AND ( x1_0.keyarticletype IS NULL
OR x1_0.keyfreesiteaccess = 0 )
LEFT JOIN ObjectViews.dbo.AISArticleType x8_0
ON x0_0.KeyArticleType=x8_0.KeyArticleType
INNER JOIN objectviews.dbo.aisdocsreference x3_0
ON x0_0.keydoc = x3_0.keydoc
INNER JOIN objectviews.dbo.instnfiling x4_0
ON x3_0.keydocreferenced = x4_0.keydoc
INNER JOIN objectviews.dbo.instnfilingtype x5_0
ON x4_0.keyinstnfilingtype = x5_0.keyinstnfilingtype
INNER JOIN objectviews.dbo.docsfilingalias x6_0
ON x5_0.filingalias = x6_0.alias
INNER JOIN objectviews.dbo.documentfile g
ON x4_0.keydoc = g.keydoc
AND x6_0.keyinteractiveservicelevel = 0
AND ( ( x5_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 ) )
WHERE x0_0.keydoc = @KeyDoc
March 9, 2012 at 12:22 am
Try this, it may contains syntax error. I don't have a context, so this is just a wild idea. We need to optimize this. Try and let me know you are getting result or not:
SELECT DISTINCT a.keydoc,
a.keyfreesiteaccess,
a.keyfreesiteaccessoverride,
a.keyfreesiteaccesspublishable
FROM objectviews..aisarticle a
JOIN (SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN x0_0.keyfreesiteaccessoverrie
END AS keyfreesiteaccessoverride,
x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
WHERE x0_0.keyfreesiteaccessoverride IS NOT NULL ) b
on a.keydoc = b.keydoc
JOIN (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 ) c
on a.keydoc = c.keydoc
join (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.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 ) ) d
on a.keydoc = d.keydoc
join (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.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 ) ) ) e
on a.keydoc = e.keydoc
WHERE x0_0.keydoc = @KeyDoc
March 9, 2012 at 1:43 am
There were some changes in the query that you have provided but it helps a lot and sort out my problem.
Thanks a lot for your prompt reply.
March 19, 2012 at 4:38 am
iam777here4u (3/9/2012)
Try this, it may contains syntax error. I don't have a context, so this is just a wild idea. We need to optimize this. Try and let me know you are getting result or not:SELECT DISTINCT a.keydoc,
a.keyfreesiteaccess,
a.keyfreesiteaccessoverride,
a.keyfreesiteaccesspublishable
FROM objectviews..aisarticle a
JOIN (SELECT CASE
WHEN x0_0.keydoc = @KeyDoc THEN x0_0.keyfreesiteaccessoverrie
END AS keyfreesiteaccessoverride,
x0_0.keydoc
FROM objectviews.dbo.aisarticle x0_0
WHERE x0_0.keyfreesiteaccessoverride IS NOT NULL ) b
on a.keydoc = b.keydoc
JOIN (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 ) c
on a.keydoc = c.keydoc
join (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.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 ) ) d
on a.keydoc = d.keydoc
join (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.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 ) ) ) e
on a.keydoc = e.keydoc
WHERE x0_0.keydoc = @KeyDoc
Hey i just have a one quick question.
This is definitely working at what I suspected.The Logical Reads are so low because the left joins to the sub-queries are being removed.but what does it accomplish? I did look at the query plan and found that the subqueries aren't even being run.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply