How to use same WHEN clause with multiple THEN cases e.g:
CASE WHEN COUNT(t2.test)=2 THEN t2.costs/2 ELSE t2.costs END costs,
CASE WHEN COUNT(t2.test)=2 THEN t2.CIR/2 ELSE t2.CIR END CIR,
CASE WHEN COUNT(t2.test)=2 THEN t2.CPV/2 ELSE t2.CPV END CPV
Is it possible to have one WHEN clause, since in all cases it is same?
Here's an example of evaluating for non-nulls (existing id) in several tables in sequence, joined with LEFT OUTERs. The first table with a matching row assigns the 'phase' of the query row.
CASE COALESCE(a.id, p.id, f.id, g.id, c.id)
WHEN a.id THEN 'Arb'
WHEN p.id THEN '3'
WHEN f.id THEN '2'
WHEN g.id THEN '1'
WHEN c.id THEN '0'
ELSE '0'
END as phase
Your code:
CASE WHEN COUNT(t2.test)=2 THEN t2.costs/2 ELSE t2.costs END costs,
CASE WHEN COUNT(t2.test)=2 THEN t2.CIR/2 ELSE t2.CIR END CIR,
CASE WHEN COUNT(t2.test)=2 THEN t2.CPV/2 ELSE t2.CPV END CPV
All case statement expressions seem to generate a new column value, and hence can't be used under a single case-when clause.
And as per documentation, for each of the expression's value, you have to use a when clause.
CASE COUNT(t2.test)
WHEN 2 THEN t2.costs/2
WHEN 3 THEN some_other_expression_here
ELSE t2.costs
END costs,
CASE COUNT(t2.test)
WHEN 2 THEN t2.CIR/2
WHEN 3 THEN another_expression_here
ELSE t2.CIR
END CIR,
CASE another_case_expression
WHEN ...
ELSE ...
END as value_alias_name
You can't just check a single when at one place and use it at multiple column expressions.
Refer to: