using a case statement in sql for jasperserver report

0

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
 
dane.woodhead's picture
Joined: Mar 3 2015 - 3:22am
Last seen: 5 years 7 months ago

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.

hozawa - 5 years 7 months ago

0 Answers:

No answers yet
Feedback
randomness