using a case statement in sql for jasperserver report

[dane.woodhead]

hi community,

i am trying to use a case statement in sql for my report and i keep getting when null ='PUKPRN' syntax error

SELECT observe.obs_pfm_10004823.obs_del_main_asor, observe.obs_pfm_10004823.obs_lnr_uln, observe.obs_pfm_10004823.obs_del_lrn, observe.obs_pfm_10004823.obs_lnr_last, observe.obs_pfm_10004823.obs_lnr_first, CAST(observe.obs_pfm_10004823.obs_del_lsd as DATE) as obs_del_lsd, CAST(observe.obs_pfm_10004823.obs_del_ped as DATE) as obs_del_ped, CAST(observe.obs_pfm_10004823.obs_del_hed as DATE) as obs_del_hed, observe.obs_pfm_10004823.obs_del_state, CAST(observe.obs_pfm_10004823.obs_del_led as DATE) as obs_del_led, observe.obs_pfm_10004823.obs_del_ssa_t1_title from observe.obs_pfm_10004823

WHERE

    CASE

        WHEN $P{FLT_TYPE} = 'ASOR' THEN

            observe.obs_pfm_10004823.obs_del_main_asor = $P{FLT_VALUE} AND ( observe.obs_pfm_10004823.obs_del_pued = $P{PUED} OR observe.obs_pfm_10004823.obs_del_pted = $P{PTED} OR observe.obs_pfm_10004823.obs_del_pped = $P{PPED} OR observe.obs_pfm_10004823.obs_del_state = $P{VOLUME} OR observe.obs_pfm_10004823.obs_del_exmp = $P{VOLUME}) AND (observe.obs_pfm_10004823.obs_del_ped BETWEEN  $P{PED_S} AND $P{PED_E} or observe.obs_pfm_10004823.obs_del_hed BETWEEN $P{PED_S} AND $P{PED_E}) order by observe.obs_pfm_10004823.obs_lnr_uln

        WHEN $P{FLT_TYPE} = 'PUKPRN' THEN

            observe.obs_pfm_10004823.obs_del_pukprn = $P{FLT_VALUE} AND ( observe.obs_pfm_10004823.obs_del_pued = $P{PUED} OR observe.obs_pfm_10004823.obs_del_pted = $P{PTED} OR observe.obs_pfm_10004823.obs_del_pped = $P{PPED} OR observe.obs_pfm_10004823.obs_del_state = $P{VOLUME} OR observe.obs_pfm_10004823.obs_del_exmp = $P{VOLUME}) AND (observe.obs_pfm_10004823.obs_del_ped BETWEEN  $P{PED_S} AND $P{PED_E} or observe.obs_pfm_10004823.obs_del_hed BETWEEN $P{PED_S} AND $P{PED_E}) order by observe.obs_pfm_10004823.obs_lnr_uln

        WHEN $P{FLT_TYPE} = 'SSAT1' THEN

            observe.obs_pfm_10004823.obs_del_ssa_t1_title = $P{FLT_VALUE} AND ( observe.obs_pfm_10004823.obs_del_pued = $P{PUED} OR observe.obs_pfm_10004823.obs_del_pted = $P{PTED} OR observe.obs_pfm_10004823.obs_del_pped = $P{PPED} OR observe.obs_pfm_10004823.obs_del_state = $P{VOLUME} OR observe.obs_pfm_10004823.obs_del_exmp = $P{VOLUME} AND (observe.obs_pfm_10004823.obs_del_ped BETWEEN  $P{PED_S} AND $P{PED_E} or observe.obs_pfm_10004823.obs_del_hed BETWEEN $P{PED_S} AND $P{PED_E}) order by observe.obs_pfm_10004823.obs_lnr_uln

        ELSE

            ( observe.obs_pfm_10004823.obs_del_pued = $P{PUED} OR observe.obs_pfm_10004823.obs_del_pted = $P{PTED} OR observe.obs_pfm_10004823.obs_del_pped = $P{PPED} OR observe.obs_pfm_10004823.obs_del_state = $P{VOLUME} OR observe.obs_pfm_10004823.obs_del_exmp = $P{VOLUME}) AND (observe.obs_pfm_10004823.obs_del_ped BETWEEN  $P{PED_S} AND $P{PED_E} or observe.obs_pfm_10004823.obs_del_hed BETWEEN $P{PED_S} AND $P{PED_E}) order by observe.obs_pfm_10004823.obs_lnr_uln

    END

 

[hozawa]

Not an answer but just a warning. Case statement executes very slow. I've found report generation performance problem to be with sql statements using case statements.