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