December 21, 2009 at 3:20 am
Hi
I have this Select query -
SELECT a.IPEquipmentID IPEquipmentDataRowID, a.IPEquipmentName, a.IPEquipmentTypeID,
g.ManagementIPAddress, g.VoiceIPAddress, p.HDFReference,
h.IPTVStateID as IPTVStateID
FROM dbo.NADIPEquipment a WITH(NOLOCK)
LEFT JOIN (
SELECT a.IPEquipmentID,
max(ManagementIPAddress) as ManagementIPAddress,
max(VoiceIPAddress) as VoiceIPAddress
FROM (
SELECT a.IPEquipmentID,
case when c.VLanTypeID is not null then b.IPAddress end AS ManagementIPAddress,
case when e.VLanTypeID is not null then b.IPAddress end AS VoiceIPAddress
FROM dbo.NADIPEquipment a WITH(NOLOCK)
INNER JOIN dbo.NADVLanIPEquipmentIPAddress b WITH(NOLOCK) ON a.IPEquipmentID =
b.IPEquipmentID
LEFT JOIN dbo.NADVLAN c WITH(NOLOCK) on b.VLanDataRowID = c.VLanDataRowID AND c.VLanTypeID = @MgmtVLanTypeID
LEFT JOIN dbo.NADVLAN e WITH(NOLOCK) on b.VLanDataRowID = e.VLanDataRowID AND e.VLanTypeID = @VoiceVLanTypeID
) a
GROUP BY a.IPEquipmentID
) g ON g.IPEquipmentID = a.IPEquipmentID
INNER JOIN(
SELECT a.IPEquipmentID, max(IPTVStateID) as IPTVStateID
FROM (
Select a.IPEquipmentID, case when c.StateMachineTypeID is not null then b.StateID end as IPTVStateID
FROM dbo.NADIPEquipment a WITH(NOLOCK)
INNER JOIN NADBusinessFrameworkDatabase.dbo.BFDCurrentDataRowStateTime b WITH(NOLOCK) ON a.IPEquipmentID = b.IPEquipmentID AND @IPEquipmentDataTableID = b.DataTableID
LEFT JOIN NADBusinessFrameworkDatabase.dbo.BFDStateMachine c WITH(NOLOCK) ON b.StateMachineID = c.StateMachineID AND c.StateMachineTypeID = @IPTVEnabledStateMachineTypeID
) a
GROUP BY a.IPEquipmentID
)h ON h.IPEquipmentID = a.IPEquipmentID
WHERE a.IPEquipmentID = @IPEquipmentDataRowID
AND c.StateMachineTypeID = @IPEquipmentStateMachineTypeID
If I do not include the second Main Left join Sub Statement/query ( for IPTV State ID), the query is executed successfully.
Otherwise it gives an error of -
Invalid column name 'IPEquipmentID'
Where am I going wrong?
December 21, 2009 at 3:22 am
What do you get when you do a syntax check on it. Exact error please.
As an aside, I see lots of nolock. Do you know what that really does? See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 3:32 am
Gail,
This is an SP for some reports for which the users just want rough numbers and accuracy is not the highlight.
Also, there is loads of data and users working on it at the same time, hence the use of 'NOLOCK'.
Is this a reason good enough? or Should I still consider not using it?
I am still not able to get over the - Invalid column name error. 🙁
December 21, 2009 at 3:34 am
GilaMonster (12/21/2009)
What do you get when you do a syntax check on it. Exact error please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 3:37 am
One of the joins had a problem.
I was joining it with the column which did not exist for that table.
Sorry, its probably the Monday that's the cause of this stupid error.
December 21, 2009 at 3:39 am
Invalid column name error. 🙁
It was an invalid column name for IPEquipmentID
December 21, 2009 at 3:41 am
namrata.dhanawade-1143388 (12/21/2009)
Also, there is loads of data and users working on it at the same time, hence the use of 'NOLOCK'.Is this a reason good enough?
Generally no. Blocking usually is a result of non-optimal queries or poor indexing. Nolock's often used to hide the symptoms cause it's easier than fixing the root cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 4:47 am
Your output list includes a column with an alias not used in the query (which uses aliases a, g and h):
SELECT a.IPEquipmentID IPEquipmentDataRowID, a.IPEquipmentName, a.IPEquipmentTypeID,
g.ManagementIPAddress, g.VoiceIPAddress, p.HDFReference,
h.IPTVStateID as IPTVStateID
Your WHERE clause has a similar error:
WHERE a.IPEquipmentID = @IPEquipmentDataRowID
AND c.StateMachineTypeID = @IPEquipmentStateMachineTypeID
I'd make a couple of small changes to the query which could improve performance:
SELECT a.IPEquipmentID IPEquipmentDataRowID, a.IPEquipmentName, a.IPEquipmentTypeID,
g.ManagementIPAddress, g.VoiceIPAddress, p.HDFReference,
h.IPTVStateID as IPTVStateID
FROM dbo.NADIPEquipment a WITH(NOLOCK)
LEFT JOIN (
SELECT b.IPEquipmentID,
MAX(case when c.VLanTypeID is not null then b.IPAddress end) AS ManagementIPAddress,
MAX(case when e.VLanTypeID is not null then b.IPAddress end) AS VoiceIPAddress
FROM dbo.NADVLanIPEquipmentIPAddress b WITH(NOLOCK)
LEFT JOIN dbo.NADVLAN c WITH(NOLOCK)
on b.VLanDataRowID = c.VLanDataRowID
AND c.VLanTypeID = @MgmtVLanTypeID
LEFT JOIN dbo.NADVLAN e WITH(NOLOCK)
on b.VLanDataRowID = e.VLanDataRowID
AND e.VLanTypeID = @VoiceVLanTypeID
GROUP BY b.IPEquipmentID
) g ON g.IPEquipmentID = a.IPEquipmentID
LEFT JOIN (
SELECT b.IPEquipmentID,
MAX(case when c.StateMachineTypeID is not null then b.StateID end) as IPTVStateID
FROM NADBusinessFrameworkDatabase.dbo.BFDCurrentDataRowStateTime b WITH(NOLOCK)
LEFT JOIN NADBusinessFrameworkDatabase.dbo.BFDStateMachine c WITH(NOLOCK)
ON b.StateMachineID = c.StateMachineID
AND c.StateMachineTypeID = @IPTVEnabledStateMachineTypeID
WHERE b.DataTableID = @IPEquipmentDataTableID
GROUP BY b.IPEquipmentID
) h ON h.IPEquipmentID = a.IPEquipmentID
WHERE a.IPEquipmentID = @IPEquipmentDataRowID
AND c.StateMachineTypeID = @IPEquipmentStateMachineTypeID
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply