sql case in column and where section without repeating the case-CodePudding

I have a sql statement like this but with a very long case expression in it and I need it in the where as well as in the column. Is there a way to use the case without repeating it in the column and where section.

select * from c.name, (case when c.age = 1 then 0 when c.age = 2 then 1 ... etc else -1 end;)
from customer c 
where (case when c.age = 1 then 0 when c.age = 2 then 1 ... etc else -1 end;) > 0

CodePudding user response：

Typically you would use a subquery to get around this problem:

SELECT *
FROM
(
    SELECT c.name,
           CASE WHEN c.age = 1 THEN 0 WHEN c.age = 2 THEN 1 ELSE -1 END AS val
    FROM customer c
    INNER JOIN employee e ON c.emp_id = e.id
) t
WHERE val > 0;

But in practice if you had a list of age values which get mapped to positive values, you could just use a single level query with a WHERE clause:

SELECT c.name,
       CASE WHEN c.age = 1 THEN 0 WHEN c.age = 2 THEN 1 ELSE -1 END AS val
FROM customer c
WHERE age IN (2, ...);

CodePudding user response：

A simple way of consolidating would be to use a lateral join

select * 
from customer c,
lateral (values(case when c.age = 1 then 0 when c.age = 2 then 1 ... etc else -1 end))v(AgeCheck)
where AgeCheck > 0;