use select under case statement

76 views Asked by At

I want to use a query which allows me to get two differents results on depending on the filtre. I have tried this one but it does not work.

CASE WHEN filtre = 10
THEN
(
 select sum(s.MONTANT_CREANCE_EMP) as creances_emp  , s.ANNEE as annee from sc01_emp s
)
ELSE
(
select sum(s.NOMBRE_CREANCE) as creances_emp  , s.ANNEE as annee from sc01_emp s

)  
END

Thanks all.

2

There are 2 answers

6
davegreen100 On

try

SELECT 
CASE WHEN filtre = 10
THEN
(
 select sum(s.MONTANT_CREANCE_EMP) as creances_emp  , s.ANNEE as annee from sc01_emp s
)
ELSE
(
select sum(s.NOMBRE_CREANCE) as creances_emp  , s.ANNEE as annee from sc01_emp s
)  
END
from <table>
0
54l3d On

Your code does not comply with the correct syntax of case expression

select 
CASE 
(select count(*) from dual) WHEN 1 -- this is for test, change it to <filtre> WHEN 10
THEN
(SELECT SUM (MONTANT_CREANCE_EMP) from sc01_emp s) -- if true the result of this subquery will be affected to creances_emp
ELSE
(select SUM (NOMBRE_CREANCE) from sc01_emp s) -- if false the result of this subquery will be affected to creances_emp
END 
as creances_emp,
ANNEE as annee
from sc01_emp s;