Hello All,

I have query with case statement that is not working in Crystal report. Could someone please help me with how the query should be in crystal format?

Thanks,

MN



with KPI as
(select * from CMN_LOOKUPS_V where lookup_type in ('SI_KPI_STATUS') and language_code='en')
select pr.PRPROJECTID PRJ_ID,
pr.prname,
pr.prFinish,
pr.prStart,
PR.prcategory,
NVl(odf.si_trk_phase_status,-99) TRK_PHASE_STATUS,
L1.Name TRK_PHASE_STATUS_name,
odf.si_track_phase,
odf.si_trk_dep_status,
odf.si_trk_budet_status,
odf.si_trk_res_status,
odf.si_trk_scope_status,
odf.si_trk_comment,
(case
when pr.prCategory = 'Deployed' then odf.si_org_dep_date
else null
END)org_dt,
(case
when pr.prCategory ='Deployed' then odf.si_cur_dep_date
else null
END)cur_dt
from PRTASK pr
inner join odf_ca_task odf on odf.id = PR.PRid
AND odf.si_status_rpt_flag = 1
left join KPI L1 on L1.lookup_code=odf.si_trk_phase_status

Thank you. I tried the query and not getting output for org_dt and cur_dt.

I added the field outside the case statement and the data is coming up. While searching the web couple of places it was mentioned to use the DEFAULT option in the query.