CODEWITHSUNDEEP
mysqlsql
BentCoder
BentCoder

Reputation: 12740

WHERE clause to select from AS part of the query

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%')

Upvotes: 1

Views: 124

Answers (4)

Naveen
Naveen

Reputation: 1502

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%'

Upvotes: 0

Joe Harper
Joe Harper

Reputation: 470

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.

Upvotes: 0

StanislavL
StanislavL

Reputation: 57421

Replace 

WHERE (client_name LIKE '%john%')

with

HAVING (client_name LIKE '%john%')

Upvotes: 2

JDGuide
JDGuide

Reputation: 6525

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.

Upvotes: 0

Related Questions