Unknown Column In Where Clause

Question

I have a simple query:

SELECT u_name AS user_name FROM users WHERE user_name = "john";

I get Unknown Column 'user_name' in where clause. Can I not refer to 'user_name' in other parts of the statement even after select 'u_name as user_name'?

Answer 1

try your task using IN condition or OR condition and also this query is working on spark-1.6.x

 SELECT  patient, patient_id FROM `patient` WHERE patient IN ('User4', 'User3');

or

SELECT  patient, patient_id FROM `patient` WHERE patient = 'User1' OR patient = 'User2';

Answer 2

select u_name as user_name from users where u_name = "john";

Think of it like this, your where clause evaluates first, to determine which rows (or joined rows) need to be returned. Once the where clause is executed, the select clause runs for it.

To put it a better way, imagine this:

select distinct(u_name) as user_name from users where u_name = "john";

You can't reference the first half without the second. Where always gets evaluated first, then the select clause.

Answer 3

Either:

SELECT u_name AS user_name
FROM   users
WHERE  u_name = "john";

or:

SELECT user_name
from
(
SELECT u_name AS user_name
FROM   users
)
WHERE  u_name = "john";

The latter ought to be the same as the former if the RDBMS supports predicate pushing into the in-line view.

Answer 4

What about:

SELECT u_name AS user_name FROM users HAVING user_name = "john";

Answer 5

Not as far as I know in MS-SQL 2000/5. I've fallen foul of this in the past.

Answer 6

Your defined alias are not welcomed by the WHERE clause you have to use the HAVING clause for this

SELECT u_name AS user_name FROM users HAVING user_name = "john";

OR you can directly use the original column name with the WHERE

SELECT u_name AS user_name FROM users WHERE u_name = "john";

Same as you have the result in user defined alias as a result of subquery or any calculation it will be accessed by the HAVING clause not by the WHERE

SELECT u_name AS user_name ,
(SELECT last_name FROM users2 WHERE id=users.id) as user_last_name
FROM users  WHERE u_name = "john" HAVING user_last_name ='smith'