Using Alias in the WHERE Clause SQL

Question

What I'm trying to do is narrow the query down in the from clause by using WHERE LienType = 'No Private' from the Private Benefits alias created from the case statement. How would I do so?

SELECT DISTINCT ClientId
,ClientFirstName
,ClientLastName
, Case when (select COUNT(*)
              from FullProductView fpvb 
              where fpvb.clientid=fpva.clientid 
              and fpvb.Lientype ='Private Lien') = 0 then 'No Private' 
         else 'Private Yes' end 'Private Benefits'
from fullproductview fpva 
WHERE CaseId = 420
Order By 'Private Benefits'

I think solving his issue is not duplicate but a restructure of the query: SELECT ClientId, ClientFirstName, ClientLastName, CASE WHEN SUM(CASE WHEN LienType = 'Private Lien' THEN 1 ELSE 0 END) > 0 THEN 'Private Yes' ELSE 'Private No' END PrivateBenefits FROM FullProductView FPVA WHERE CaseId = 420 GROUP BY ClientId, ClientFirstName, ClientLastName ORDER BY CASE WHEN SUM(CASE WHEN LienType = 'Private Lien' THEN 1 ELSE 0 END) > 0 THEN 'Private Yes' ELSE 'Private No' END; — K Richard, Jan 14 '15 at 15:26

ericpap · Answer 1 · 2015-01-14T15:20:54.287

You can't directly. The only two options to use a calculated field on the WHERE clause is to create a view, with the calculated field, or a subquery. I think that to create a view is the easy way.

EDIT

Try this:

CREATE VIEW vw_MyView AS
SELECT DISTINCT ClientId
,ClientFirstName
,ClientLastName
, Case when (select COUNT(*)
              from FullProductView fpvb 
              where fpvb.clientid=fpva.clientid 
              and fpvb.Lientype ='Private Lien') = 0 then 'No Private' 
         else 'Private Yes' end 'Private Benefits'
from fullproductview fpva 
WHERE CaseId = 420

Then you can do:

SELECT * from vw_MyView WHERE LienType = 'No Private' Order By 'Private Benefits'

Also is not a good idea to use spaces on field names. You need to check if this works with your database engine (wich you don't mention).

How would you structure the view? – EAJ5 Jan 14 '15 at 15:15 — EAJ5, Jan 14 '15 at 15:15

Using Alias in the WHERE Clause SQL

1 Answers1