Clever Geek Handbook

How do I write a WHERE clause based on the value of the CASE statement?

The following query returns the result set of all new_name

from the tablenames 

SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names

I am trying to search for new_name

using the following query

SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names
    WHERE new_name LIKE '%Joh%'

However I am getting the error

Unknown column "new_name" in clause "where"

Is there a way to make this functionality work without using a subquery?

+3


source to share


3 answers


If you repeat case

in a sentence where

, the instruction will work:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (CASE WHEN nickname = '' THEN fullname ELSE nickname END) LIKE '%Joh%'

It won't be fast because it won't use indexes, but it should work.



A somewhat better approach would be as follows:

SELECT
CASE
    WHEN nickname = '' THEN fullname ELSE nickname END AS new_name      
FROM names
WHERE (nickname like '%Joh%') or (fullname LIKE '%Joh%')

This will return the same results, but indices on nickname

and fullname

, if you define them, EDIT can be used and change the second operand LIKE

to not use leading %

.

+5


source 


SELECT
    CASE
        WHEN nickname = '' THEN fullname ELSE nickname END AS new_name,       
    FROM names
    WHERE 
       (CASE
        WHEN nickname = '' THEN fullname ELSE nickname END) LIKE '%Joh%';


replace the column name with everything case ... when ... then ... end

+2


source


It makes sense for me to use the coalesce function, assuming the alias NULL

is either complete ...

SELECT COALESCE(nickname,filename)
FROM   table
WHERE COALESCE(nickname,filename) LIKE '%JOE%'
+1


source






More articles:


All Articles