?
Solved

T-SQL Using Subquery result in HAVING clause

Posted on 2009-04-17
5
Medium Priority
?
797 Views
Last Modified: 2012-05-06
Hi,
I want to use the result of a subquery in the having clause but does not work the way it is set up in my query.
I want to clean the table from rows with multiple PLogicalID in my EventLog for any given Serial
SELECT     EventLog_1.Serial, PLogical_1.PLogicalName, PHardwareA_1.PLogicalID, EventLog_1.DateTime, PLogical_1.Location,
                          (SELECT     PHardwareA.PLogicalID
                            FROM          EventLog INNER JOIN
                                                   PHardwareA ON EventLog.PHardwareAID = PHardwareA.PHardwareAID INNER JOIN
                                                   PLogical ON PHardwareA.PLogicalID = PLogical.PLogicalID
                            WHERE      (EventLog.EventLogID = EventLog_1.EventLogID - 1)) AS PLogicalIDAlt
FROM         EventLog AS EventLog_1 INNER JOIN
                      PHardwareA AS PHardwareA_1 ON EventLog_1.PHardwareAID = PHardwareA_1.PHardwareAID INNER JOIN
                      PLogical AS PLogical_1 ON PHardwareA_1.PLogicalID = PLogical_1.PLogicalID
GROUP BY EventLog_1.Serial, PLogical_1.PLogicalName, EventLog_1.DateTime, PLogical_1.Location, PHardwareA_1.PLogicalID, 
                      EventLog_1.EventLogID
HAVING PHardwareA_1.PLogicalID <> PLogicalIDAlt
ORDER BY EventLog_1.Serial, EventLog_1.DateTime

Open in new window

0
Comment
Question by:JoeUS
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24168961
HAVING is for aggregated columns. Give this a try:
SELECT  DISTINCT
	EventLog_1.Serial, 
	PLogical_1.PLogicalName, 
	PHardwareA_1.PLogicalID, 
	EventLog_1.DateTime, 
	PLogical_1.Location,
        (SELECT     PHardwareA.PLogicalID
        	FROM          EventLog 
		INNER JOIN PHardwareA ON EventLog.PHardwareAID = PHardwareA.PHardwareAID 
		INNER JOIN PLogical ON PHardwareA.PLogicalID = PLogical.PLogicalID
 		WHERE      (EventLog.EventLogID = EventLog_1.EventLogID - 1)) AS PLogicalIDAlt
 
FROM         EventLog AS EventLog_1 
 
INNER JOIN PHardwareA AS PHardwareA_1 ON EventLog_1.PHardwareAID = PHardwareA_1.PHardwareAID 
INNER JOIN PLogical AS PLogical_1 ON PHardwareA_1.PLogicalID = PLogical_1.PLogicalID
WHERE PHardwareA_1.PLogicalID <> PLogicalIDAlt
 
ORDER BY EventLog_1.Serial, 
	EventLog_1.DateTime

Open in new window

0
 

Author Comment

by:JoeUS
ID: 24169372
It gives me "Invalid column name" on PLogicalIDAlt
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 24169413
You cannot reference a sub-query column, or a calculated field, by it's alias like that.  You would have to place the entire thing in a derived table.
select * from (SELECT  DISTINCT
	EventLog_1.Serial, 
	PLogical_1.PLogicalName, 
	PHardwareA_1.PLogicalID, 
	EventLog_1.DateTime, 
	PLogical_1.Location,
        (SELECT     PHardwareA.PLogicalID
        	FROM          EventLog 
		INNER JOIN PHardwareA ON EventLog.PHardwareAID = PHardwareA.PHardwareAID 
		INNER JOIN PLogical ON PHardwareA.PLogicalID = PLogical.PLogicalID
 		WHERE      (EventLog.EventLogID = EventLog_1.EventLogID - 1)) AS PLogicalIDAlt
 
FROM         EventLog AS EventLog_1 
 
INNER JOIN PHardwareA AS PHardwareA_1 ON EventLog_1.PHardwareAID = PHardwareA_1.PHardwareAID 
INNER JOIN PLogical AS PLogical_1 ON PHardwareA_1.PLogicalID = PLogical_1.PLogicalID
)a
WHERE PLogicalID <> PLogicalIDAlt
ORDER BY Serial, DateTime

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 1600 total points
ID: 24170272
Yeah, missed that. What about this then?

SELECT  DISTINCT
	EventLog_1.Serial, 
	PLogical_1.PLogicalName, 
	PHardwareA_1.PLogicalID, 
	EventLog_1.DateTime, 
	PLogical_1.Location,
	s.PLogicalIDAlt 
FROM         EventLog AS EventLog_1 
 
INNER JOIN PHardwareA AS PHardwareA_1 ON EventLog_1.PHardwareAID = PHardwareA_1.PHardwareAID 
INNER JOIN (SELECT     EventLog.EventLogID, PHardwareA.PLogicalID as PLogicalIDAlt
        	FROM          EventLog 
		INNER JOIN PHardwareA ON EventLog.PHardwareAID = PHardwareA.PHardwareAID
	   ) s ON s.EventLogID = (EventLog_1.EventLogID - 1)
 
INNER JOIN PLogical AS PLogical_1 ON PHardwareA_1.PLogicalID = PLogical_1.PLogicalID
WHERE PHardwareA_1.PLogicalID <> s.PLogicalIDAlt
 
ORDER BY EventLog_1.Serial, 
	EventLog_1.DateTime

Open in new window

0
 

Author Closing Comment

by:JoeUS
ID: 31571502
You guys are geniuses! Worked perfectly. Thank you very much.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question