Geek Answers Handbook

Using a subquery pseudo-layer in a WHERE clause

I am trying to use an alias created in SELECT, but in a WHERE statement. I know this does not work, and I just read why in another SO question.

But my question is: what other decision should I take to make this work without repeating the subquery?

SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG FROM Patients p WHERE AvgBG > 60;

I know this works:

 SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG FROM Patients p WHERE (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) > 60;

But I do not want to repeat this subquery. And I'm sure this is not very convenient, so I ask for a better solution here.

Thanks!

+4
source share
7 answers

Try using a view instead.

 SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG FROM Patients p JOIN (SELECT PatientID, AVG(BGValue) AvgBG FROM BloodGlucose group by PatientID ) BG ON BG.PatientID = p.PatientID WHERE AvgBG > 60;
+11
source

Aliases in a WHERE clause can only arise from a FROM clause. Here is a way to rewrite your request:

 SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName as PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, av.AvgBG FROM Patients p join (SELECT PatientId, AVG(BGValue) as AvgBG FROM BloodGlucose group by PatientID ) av on p.PatientId = av.PatientId WHERE av.AvgBG > 60;
+1
source 
 SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, bg.AvgBG FROM Patients p outer apply (SELECT PatientID, AVG(BGValue) AvgBG FROM BloodGlucose where PatientID = p.PatientID group by PatientID ) BG WHERE AvgBG > 60;

it should also work pretty fast

+1
source

You can try using the WITH clause in standard SQL.

 WITH Subquery AS ( Your Code )

And then your code

 SELECT .... Subquery As sq From Patients p WHERE(subquery)>60
0
source

Use a generic table expression:

 ;WITH CTE (PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG) AS ( SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName + ' ' + p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, (SELECT AVG(BGValue) FROM BloodGlucose WHERE PatientID = p.PatientID) AvgBG FROM Patients p ) SELECT PatientID, PatientType, AccountNumber, PatientFullName, CreatedDate, TotalTime, AvgBG FROM CTE WHERE AvgBG > 60
0
source

@HLGEM, I'm not quite sure that this is exactly what you intended to say: "These are more likely subject errors, such as random cross joins and incorrect results, if you try to use the left join and the right join "

0
source 
 SELECT p.PatientID, p.PatientType, p.AccountNumber, p.FirstName p.LastName PatientFullName, p.CreatedDate, DATEDIFF(hour, p.CreatedDate, GETDATE()) TotalTime, a.AvgBG FROM Patients p, (SELECT PatientID, AVG(BGValue) AvgBG FROM BloodGlucose group by PatientID having AVG(BGValue) > 60) a where p.PatientID = a.PatientID
-1
source

More articles:


All Articles