WHERE clause to select from AS part of the query

Question

When I don't use WHERE clause query works fine. How can I make it work with WHERE clause? Is it too ambitious?

Thanks

ERROR:

[Err] 1054 - Unknown column 'claims.client_name' in 'where clause'

SQL:

SELECT
(
    CASE
        WHEN client = 'Driver' THEN (SELECT fullname FROM driver WHERE id = claims.client_id)
        WHEN client = 'Passenger' THEN (SELECT fullname FROM passengers WHERE id = claims.client_id)
        WHEN client = 'Thirdparty' THEN (SELECT fullname FROM thirdparty WHERE id = claims.client_id)
        WHEN client = '' THEN  NULL
    END
) AS client_name

FROM claims

WHERE (client_name LIKE '%john%')

You can't refer alias name in WHERE clause. Check the answers here http://stackoverflow.com/questions/8370114/referring-to-a-column-alias-in-a-where-clause — Naveen, Mar 18 '14 at 11:45
Using `HAVING` instead of `WHERE` would probably be best in this case. — Vatev, Mar 18 '14 at 11:48
you should try this `WHERE (claims.client_name LIKE '%john%')` — jmail, Mar 18 '14 at 11:53

score 2 · Accepted Answer · answered Mar 18 '14 at 11:53

2

Replace

WHERE (client_name LIKE '%john%')

with

HAVING (client_name LIKE '%john%')

answered Mar 18 '14 at 11:53

StanislavL

56,971
9
68
98

Such an easy and simple solution. Never thought about it. Thanks – BentCoder Mar 18 '14 at 11:58

score 0 · Answer 2 · answered Mar 18 '14 at 11:48

Try this :-

SELECT  CASE WHEN client = 'Driver' THEN (SELECT fullname FROM driver WHERE id = claims.client_id)
        WHEN client = 'Passenger' THEN (SELECT fullname FROM passengers WHERE id = claims.client_id)
        WHEN client = 'Thirdparty' THEN (SELECT fullname FROM thirdparty WHERE id = claims.client_id)
        WHEN client = '' THEN  NULL
        END AS client_name FROM claims WHERE client_name LIKE '%john%'

Hope it will help you.

Joe Harper · Answer 3 · 2014-03-18T11:57:09.150

You can't use an alias in the where clause. Generally you would repeat the code which you give the alias to and put that in.

ie, instead of putting "WHERE client_name LIKE '%john%'" you could try:

WHERE (CASE
         WHEN client = 'Driver' THEN (SELECT fullname FROM driver WHERE id =
         claims.client_id)
         WHEN client = 'Passenger' THEN (SELECT fullname FROM passengers WHERE id = 
         claims.client_id)
         WHEN client = 'Thirdparty' THEN (SELECT fullname FROM thirdparty WHERE id =
         claims.client_id)
         WHEN client = '' THEN  NULL 
       END) LIKE '%john%'

I haven't tried this in the development tool, so it's possible it needs a bit of polishing.

score 0 · Answer 4 · edited May 23 '17 at 12:05

You can't use alias name directly in WHERE clause. Check the answers HERE. Unfortunately you can't use CTE which is available in SQL server either, instead you can use
temporary tables.

Try the below statement

SELECT tmp.* FROM
(
   SELECT CASE
        WHEN client = 'Driver' THEN (SELECT fullname FROM driver WHERE id = claims.client_id)
        WHEN client = 'Passenger' THEN (SELECT fullname FROM passengers WHERE id = claims.client_id)
        WHEN client = 'Thirdparty' THEN (SELECT fullname FROM thirdparty WHERE id = claims.client_id)
        WHEN client = '' THEN  NULL
    END AS client_name FROM claims
) AS tmp
WHERE tmp.client_name LIKE '%john%'

WHERE clause to select from AS part of the query

4 Answers4