how to put nested case-when condition in postgresql query

Question

i want to write nested case when condition in query to store the value that will come from one case when condition and another case when condition into same new column.to get this kind of result i am writing the query as:

(case when sq_name_new1 like format('%%%s%%',demo.name) THEN count(sq_name_new1) else (when demo.empcode is not null then count(demo.id) End) END) AS indivisual from res_scheduledjobs 

in the above query demo.name column comes from CTE.so my whole query look like:

with demo(empcode,id,name) as                (select hr_employee.emp_code,hr_employee.id,concat(resource_resource.name,' ',hr_employee.middle_name,' ',hr_employee.last_name) as name from hr_employee inner join  resource_resource on resource_resource.id=hr_employee.resource_id) select demo.empcode,demo.name,sq_name_new1,(case when sq_name_new1 like format('%%%s%%',demo.name) THEN count(sq_name_new1) else (when demo.empcode is not null then count(demo.id) End) END) AS indivisual from res_scheduledjobs LEFT JOIN demo on demo.id=res_scheduledjobs.assigned_technician group by res_scheduledjobs.assigned_technician,sq_name_new1,demo.empcode,demo.name ; 

i just want to store the count of (sq_name_new1) column into INDIVISUAL Column and the count of (demo.id) column into same column,that is in INDIVISUAL,if the first case condition does not match. but when i am executing my query it throw an error.that is,something is wrong in the syntax of case when condition.

please help me yo write the correct nested case-when condition.

Answer 1

CASE ... WHEN ... END is an expression. It can be nested like any other expression.

CASE      WHEN condition THEN          CASE              WHEN othercondition THEN                  ....          END END