Filter Based on an Aliased Column Name

Filter based on an aliased column name

You can't reference aliases in a where clause like that... you either have to duplicate the CASE in the WHERE, or you can use a subquery like this:

SELECT id, myAlias
FROM
(
    SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias
    FROM myTable
) data
WHERE myAlias IS NOT NULL

Filtering an aliased column

wrap your sql into a sub query so you can filter on the aliases

SELECT * FROM (
SELECT A1.LRN, A1.StartDate, A1.Destination, 
(
    SELECT TOP 1 A2.StartDate
    FROM testEnrolment As A2
    WHERE A2.LRN = A1.LRN AND A2.StartDate > A1.StartDate
    ORDER BY A2.StartDate
) As NextStartDate, 
(
    SELECT TOP 1 B2.Destination
    FROM testEnrolment As B2
    WHERE B2.LRN = A1.LRN AND B2.StartDate > A1.StartDate
    ORDER BY B2.StartDate
) As NextDestination
FROM testEnrolment As A1
) AS s
WHERE NextDestination <> 'Education'

sql filtering results of alias column

Use Derived table

select t.* from
(
SELECT
loan.lnr
,loan.lamount - SUM (memrepay.mprinc) AS OLB
,lstatus

FROM
 loan
  LEFT OUTER JOIN memrepay
  ON loan.lnr = memrepay.lnr

WHERE
loan.tstart >= N'2013-07-07T14:00:00' 

GROUP BY loan.lnr, lamount, lstatus
) as t where OLB>0
Order By lnr

or use HAVING clause and use the expression

SELECT
loan.lnr
,loan.lamount - SUM (memrepay.mprinc) AS OLB
,lstatus

FROM
 loan
  LEFT OUTER JOIN memrepay
  ON loan.lnr = memrepay.lnr
WHERE
loan.tstart >= N'2013-07-07T14:00:00' 

GROUP BY loan.lnr, lamount, lstatus
HAVING loan.lamount - SUM (memrepay.mprinc) >0
Order By lnr

How to filter based on the alias instead of the column name?

You can wrap your query in another SELECT and use alias from emp_name the first SELECT as a field name in it :

SELECT x.* 
FROM (SELECT a.emp_num
           , a.emp_num ||'-'|| a.name AS emp_name
      FROM Employees a 
) x
WHERE x.emp_name LIKE '' 

If you don't want to use a subsidiary outer SELECT then you can just pass the whole concatenated string in you WHERE clause :

SELECT a.emp_num
     , a.emp_num ||'-'|| a.name AS emp_name
FROM Employees a 
WHERE a.emp_num ||'-'|| a.name LIKE ''

Can't filter by alias

Alternatively, if you want to use the alias to filter, then put the data into a CTE first:

WITH cte_name AS (
     SELECT
      id, 
      created_at,
      extract(epoch from CURRENT_TIMESTAMP - created_at) as delta_sec
    FROM shop_order
)
SELECT *
    FROM cte_name
    WHERE delta_sec > 10000

How to use alias column name in where clause in SQL Server

You can't use aliased columns in a WHERE clause. You can try using a derived table. Perhaps something like this (sorry, not tested):

SELECT * 
FROM (
    SELECT SQRT( POWER( cast(Program_Latitude as float) 
                        - cast('41.5126237' as float), 2) 
               + POWER( cast(Program_Longitude as float) 
                        - cast('-81.6516411' as float), 2)
               ) * 62.1371192    AS DistanceFromAddress 
    from tblProgram 
    ) mytable
WHERE DistanceFromAddress < 2

---

Related Topics