-1

i'm using spagobi with oracle DBMS but when i want to get values where year between 2010 and 2014 a got error : right parenthesis missing

select (sum(d.taux_depot *100)/count(r.trimestre) ) as taux , trimestre as trimestre 
from datamart_cnss d , ref_temps r 
where d.ID_TEMPS = r.ID_TEMPS 
and (case when $P{anneecnss}=123 then (r.annee between 2010 and 2014 )  else $P{anneecnss} end) = r.annee   
and (case when To_CHAR($P{regimecnss})=123 then To_CHAR(d.id_regime) else To_CHAR($P{regimecnss}) end) = To_CHAR(d.id_regime) 
and (case when To_CHAR($P{bureau_cnss})=123 then To_CHAR(d.id_bureau) else To_CHAR($P{bureau_cnss}) end) = To_CHAR(d.id_bureau)
group by trimestre 
order by trimestre asc

Thank you

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76
naruto
  • 19
  • 4

1 Answers1

0

This is not a valid construct:

case when $P{anneecnss}=123 then (r.annee between 2010 and 2014 )  else $P{anneecnss} end

You cannot have a condition inside the then part, just a value or expression that you can then compare with something else.

To apply that filter selectively you don't need to use a case statement, use and and or; I think this is equivalent:

where d.ID_TEMPS = r.ID_TEMPS 
and (($P{anneecnss} = 123 and r.annee between 2010 and 2014)
  or ($P{anneecnss} != 123 and $P{anneecnss} = r.annee))
and ($P{regimecnss} = 123 or To_CHAR($P{regimecnss}) = To_CHAR(d.id_regime))
and ($P{bureau_cnss} = 123 or To_CHAR($P{bureau_cnss}) = To_CHAR(d.id_bureau))
...
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Incidentally, having `to_char()` on both sides of a check looks suspicious; if those are all numbers then you don't want any of them to be converted to strings. – Alex Poole Mar 04 '15 at 19:08
  • my filter $P{anneecnss} contain : values of column anneecnss (2010 , 2011 , 2012... ) and a fixed value equal to 123 so the result that I want to get is : when my parameter = 123 then i get values correspending to years between 2010 and 2014 else i get values correspending to my choice from the filter which will be 2013 , 2012 or 2015.... and not 123 – naruto Mar 04 '15 at 19:19
  • @naruto - yes, that's what my approach is trying to do; have you tried it? If it doesn't work, what does happen? – Alex Poole Mar 04 '15 at 19:26