June 19, 2014 at 7:19 am
sf
June 19, 2014 at 7:21 am
I'm not sure what you're trying to do here. The subject says you want to rewrite the two into one, but the first is a SELECT and the second one's an INSERT. On another note, what's with the NOLOCK everywhere? Do you know what that does to the accuracy of the data?
June 19, 2014 at 7:23 am
The first one also inserting into #TEMP_SUBSYS, please double check
June 19, 2014 at 7:35 am
Why not use a UNION .. ?
SELECT
C.SubSystemID ,
c.SubSystemName ,
consys.SystemID ,
conSys.SystemDisplayName ,
CustomerName = ISNULL(custMaster.CustomerName , '') ,
CustomerLocation = ISNULL(facMaster.FacilityName , '')
INTO #TEMP_SUBSYS
FROM
dbo.Subsystem c WITH (NOLOCK)
INNER JOIN DBO.ControllerSystem conSys WITH (NOLOCK)
ON conSys.SystemID = c.ParentID
AND consys.ParentLevelId = 5 -- to ignore rogue records
INNER JOIN dbo.FacilityMaster facMaster WITH (NOLOCK)
ON facMaster.FacilityID = consys.ParentID
INNER JOIN dbo.CustomerMaster custMaster WITH (NOLOCK)
ON custMaster.CustomerId = facMaster.CustomerId
WHERE
facMaster.FacilityID IN (SELECT [values] FROM #TEMP_CUSTLOCFILTER)
OR @CustomerLocation_Filter = '';
UNION
---2 nd query
SELECT
C.SubSystemID ,
c.SubSystemName ,
consys.SystemID ,
conSys.SystemDisplayName ,
CustomerName = ISNULL(custMaster.CustomerName , '') ,
CustomerLocation = ISNULL(facMaster.FacilityName , '')
FROM
dbo.Subsystem c WITH (NOLOCK)
INNER JOIN DBO.ControllerSystem conSys WITH (NOLOCK)
ON conSys.SystemID = c.ParentID
AND consys.ParentLevelId = 6 -- to ignore rogue records
INNER JOIN SubLocation sl WITH (NOLOCK)
ON sl.SubLocationId = conSys.ParentID
INNER JOIN dbo.FacilityMaster facMaster WITH (NOLOCK)
ON facMaster.FacilityID = sl.FacilityId
INNER JOIN dbo.CustomerMaster custMaster WITH (NOLOCK)
ON custMaster.CustomerId = facMaster.CustomerId
WHERE
facMaster.FacilityID IN (SELECT [values] FROM #TEMP_CUSTLOCFILTER)
OR @CustomerLocation_Filter = '';
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 19, 2014 at 7:36 am
It looks like the only difference is that the first query will have consys.ParentLevelId = 5 and the second one 6? Just adjust your join condition slightly.
SELECT DISTINCT C.SubSystemID
, c.SubSystemName
, consys.SystemID
, conSys.SystemDisplayName
, CustomerName = Isnull(custMaster.CustomerName, '')
, CustomerLocation = Isnull(facMaster.FacilityName, '')
INTO #TEMP_SUBSYS
FROM dbo.Subsystem c
INNER JOIN DBO.ControllerSystem conSys ON conSys.SystemID = c.ParentID
AND consys.ParentLevelId IN ( 5, 6 ) -- to ignore rogue records
INNER JOIN dbo.FacilityMaster facMaster ON facMaster.FacilityID = consys.ParentID
INNER JOIN dbo.CustomerMaster custMaster ON custMaster.CustomerId = facMaster.CustomerId
WHERE facMaster.FacilityID IN
(
SELECT [values]
FROM #TEMP_CUSTLOCFILTER
)
OR @CustomerLocation_Filter = '';
And seriously...the NOLOCK hint is not a good thing. There are times it makes sense but seeing it littered throughout your query makes me shudder. Are your users ok with missing and/or duplicate data in your queries? This can and will happen and it is very difficult to debug because you can't reproduce it in a production environment easily. Take a look at these articles which explain what that hint really does.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
_______________________________________________________________
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/
June 19, 2014 at 7:39 am
Sean Lange (6/19/2014)
It looks like the only difference is that the first query will have consys.ParentLevelId = 5 and the second one 6? Just adjust your join condition slightly.
SELECT DISTINCT C.SubSystemID
, c.SubSystemName
, consys.SystemID
, conSys.SystemDisplayName
, CustomerName = Isnull(custMaster.CustomerName, '')
, CustomerLocation = Isnull(facMaster.FacilityName, '')
INTO #TEMP_SUBSYS
FROM dbo.Subsystem c
INNER JOIN DBO.ControllerSystem conSys ON conSys.SystemID = c.ParentID
AND consys.ParentLevelId IN ( 5, 6 ) -- to ignore rogue records
INNER JOIN dbo.FacilityMaster facMaster ON facMaster.FacilityID = consys.ParentID
INNER JOIN dbo.CustomerMaster custMaster ON custMaster.CustomerId = facMaster.CustomerId
WHERE facMaster.FacilityID IN
(
SELECT [values]
FROM #TEMP_CUSTLOCFILTER
)
OR @CustomerLocation_Filter = '';
And seriously...the NOLOCK hint is not a good thing. There are times it makes sense but seeing it littered throughout your query makes me shudder. Are your users ok with missing and/or duplicate data in your queries? This can and will happen and it is very difficult to debug because you can't reproduce it in a production environment easily. Take a look at these articles which explain what that hint really does.
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/[/url]
Sean, you missed the second query actually adds in the SubLocation table. 🙁
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 19, 2014 at 7:39 am
in second query we have ParentLevelId = 6 and added additional join on sublocation so can't it be rewritten in single statement
June 19, 2014 at 7:45 am
Jason Selburg (6/19/2014)
Sean, you missed the second query actually adds in the SubLocation table. 🙁
Ahh so I did. Then it seems the UNION query you posted would be a good approach. Might change that to a UNION ALL so it doesn't have to look for unique rows.
_______________________________________________________________
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/
June 19, 2014 at 7:49 am
Sean Lange (6/19/2014)
Jason Selburg (6/19/2014)
Sean, you missed the second query actually adds in the SubLocation table. 🙁Ahh so I did. Then it seems the UNION query you posted would be a good approach. Might change that to a UNION ALL so it doesn't have to look for unique rows.
The OP had DISTINCT for each, so UNION is best here. 😛
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 19, 2014 at 10:57 am
Hey, ramrajan, I apologize for missing the INTO in your OP. The formatting threw me and I just missed it. I'm glad that Sean and Jason were able to help you out. The workload just got away from me today.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply