CODEWITHSUNDEEP
mysqlsqlfilteringwhere-clausecolumn-alias
Mohammad Alamri
Mohammad Alamri

Reputation: 27

Cannot use alias from SELECT clause in the WHERE clause

USE stormtrooper_java;

SELECT imperial_battlegroup.BGID, imperial_battlegroup.Designation, imperial_battlegroup.HQ_LocationX, imperial_battlegroup.HQ_LocationY,
stormtrooper_unit.STUID, stormtrooper_unit.UnitCmd, stormtrooper_unit.UnitType, stormtrooper_unit.Location_X, stormtrooper_unit.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup inner join
     stormtrooper_unit
     on imperial_battlegroup.BGID = stormtrooper_unit.UnitCmd
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' AND 
      XYRange > 100;

When I execute the file without XYRange > 100 it works very well, but I do need that filtering logic in the query.

How can I adjust my query to filter the results by this calculated condition?

Upvotes: 0

Views: 70

Answers (3)

Ashraf Awad
Ashraf Awad

Reputation: 26

Its forbidden to use alias in where clause. So use the function itself

where ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y)> 100

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

SQL does not allow you to use column aliases in WHERE clauses. It does extend the HAVING, so one solution is:

SELECT bg.BGID, bg.Designation, imperial_battlegroup.HQ_LocationX, bg.HQ_LocationY,
        u.STUID, u.UnitCmd, u.UnitType, u.Location_X, u.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup bg inner join
     stormtrooper_unit
     on bg.BGID = u.UnitCmd
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' 
HAVING XYRange > 100;

Another option is to repeat the expression in the WHERE.

Upvotes: 1

Rahul Jain
Rahul Jain

Reputation: 1399

Try this:

SELECT imperial_battlegroup.BGID, imperial_battlegroup.Designation, 
imperial_battlegroup.HQ_LocationX, imperial_battlegroup.HQ_LocationY,
stormtrooper_unit.STUID, stormtrooper_unit.UnitCmd, 
stormtrooper_unit.UnitType, stormtrooper_unit.Location_X, 
stormtrooper_unit.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup inner join
stormtrooper_unit
on imperial_battlegroup.BGID = stormtrooper_unit.UnitCmd 
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' AND 
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) > 100;

Just updated the last line, rest everything is same.

Upvotes: 0

Related Questions