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?
source to share
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 %
.
source to share