if else statement within a paramter

[dane.woodhead]

hi community i seem to be getting stuck putting an if else statement within a paramter with sql, i really need some help on this thank you if you can help.

i am getting the error expecting anything but ''n''; got it anyway

with this piece of code 

$P{FLT_VALUE}.equals("ASOR")

? " Select 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, observe.obs_pfm_10004823.obs_del_lsd, observe.obs_pfm_10004823.obs_del_ped, observe.obs_pfm_10004823.obs_del_hed, observe.obs_pfm_10004823.obs_del_state, observe.obs_pfm_10004823.obs_del_led from observe.obs_pfm_10004823

WHERE observe.obs_pfm_10004823.obs_del_main_asor = %$P!{ASOR}% 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}%) and observe.obs_pfm_10004823.obs_del_ped between  %$P!{PED_S}% and %$P!{PED_E}%

order by observe.obs_pfm_10004823.obs_lnr_uln "

: $P{FLT_VALUE}.equals("PUKPRN")

? " Select 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, observe.obs_pfm_10004823.obs_del_lsd, observe.obs_pfm_10004823.obs_del_ped, observe.obs_pfm_10004823.obs_del_hed, observe.obs_pfm_10004823.obs_del_state, observe.obs_pfm_10004823.obs_del_led from observe.obs_pfm_10004823

WHERE observe.obs_pfm_10004823.obs_del_pukprn = %$P!{PUKPRN}% 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}%) and observe.obs_pfm_10004823.obs_del_ped between  %$P!{PED_S}% and %$P!{PED_E}%

order by observe.obs_pfm_10004823.obs_lnr_uln "

: $P{FLT_VALUE}.equals("SSAT1")

? " Select 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, observe.obs_pfm_10004823.obs_del_lsd, observe.obs_pfm_10004823.obs_del_ped, observe.obs_pfm_10004823.obs_del_hed, observe.obs_pfm_10004823.obs_del_state, observe.obs_pfm_10004823.obs_del_led from observe.obs_pfm_10004823

WHERE observe.obs_pfm_10004823.obs_del_ssa_t1_title = %$P!{SSAT1}% 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}%) and observe.obs_pfm_10004823.obs_del_ped between  %$P!{PED_S}% and %$P!{PED_E}%

order by observe.obs_pfm_10004823.obs_lnr_uln "

: " Select 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, observe.obs_pfm_10004823.obs_del_lsd, observe.obs_pfm_10004823.obs_del_ped, observe.obs_pfm_10004823.obs_del_hed, observe.obs_pfm_10004823.obs_del_state, observe.obs_pfm_10004823.obs_del_led from observe.obs_pfm_10004823

WHERE observe.obs_pfm_10004823.obs_del_main_asor = %$P!{ASOR}% 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}%) and observe.obs_pfm_10004823.obs_del_ped between  %$P!{PED_S}% and %$P!{PED_E}%

order by observe.obs_pfm_10004823.obs_lnr_uln "

[minaeimi]

Hi Dane,

What do you exactly mean by %$P!{PED_S}%. I mean by %?

Try wrap your query with parantesis like (condition) ? (exp1) : (exp2).

Suggestion: This stement is really very messy, and difficult to undrestand. Try to do abit clean up. To me it look like only the where clauses are different. so you can define your sql query like this select <columns> from <tablename> where P{where_clause}

Hope it helps.

[rajesh.sirsikar]

Hi Dane,

The Best Possible solution for your problem is Conditional Query. Please see the link below for the same

http://www.rajeshsirsikar.com/conditional-query-using-parameters-in-jaspersoft-studio/

Thanks,

Rajesh S