AnsweredAssumed Answered

Case statement in NSQL

Question asked by ma12 on Dec 2, 2013
Latest reply on Dec 3, 2013 by ma12

Hello All,

I have a query with Case statment in it. I referred all posts on how to combine Case and NSQL and wrote the query accordingly. The query is not compling properly and not able to find what is the issue. 

Thanks,

Manjusha

Query

SELECT 

       @SELECT:DIM:USER_DEF:IMPLIED:MILESTONE:T.prid:milestone_int_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:inv.id:project_int_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:T.PRNAME:PR_NAME@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:T.PRSTART:PR_START@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:T.PRFINISH:PR_FINISH@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:T.PRSTATUS:PR_STATUS@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_scope_status:SCP_STAT@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_res_status:RES_STAT@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_budet_status:BUD_STAT@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_track_phase:TRK_PHASE@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_phase_status:TRK_PHSTAT@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_dep_status:DEP_STAT@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:ODF.si_trk_comment:TRK_COMM@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:MILESTONE:T.PRCATEGORY:PR_CAT@
           
FROM   
(SELECT 
T.prname,
T.prStart,
T.prFinish, 
T.prStatus,
odf.si_trk_scope_status,
odf.si_trk_res_status,
odf.si_trk_budet_status,
odf.si_track_phase,
odf.si_trk_phase_status,
(case
when T.prCategory = 'Deployed' then odf.si_org_dep_date
else null
END)org_dt,
(case
when T.prCategory ='Deployed' then T.PRFINISH 
else null
END)cur_dt,
odf.si_trk_dep_status,
odf.si_trk_comment,
T.prcategory,
T.prid milestone_int_id,
inv.id project_int_id
FROM   prtask T 
inner join odf_ca_task odf on odf.id = t.PRid
inner join prtask N on N.PRid= odf.id
INNER  JOIN  inv_investments inv ON   t.prprojectid = inv.id 
INNER  JOIN  inv_investments invn ON  N.prprojectid = invn.id 
AND odf.si_status_rpt_flag = 1)
AND  INV.ID = @WHERE:PARAM:XML:INTEGER:/data/id/@value@
AND    @FILTER@
 
 

Error

ErrorERROR:NPT-0103: Error when trying to execute the query. Native message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00933: SQL command not properly ended SQL Text: SELECT T.prid milestone_int_id, inv.id project_int_id, T.PRNAME PR_NAME, T.PRSTART PR_START, T.PRFINISH PR_FINISH, T.PRSTATUS PR_STATUS, ODF.si_trk_scope_status SCP_STAT, ODF.si_trk_res_status RES_STAT, ODF.si_trk_budet_status BUD_STAT, ODF.si_track_phase TRK_PHASE, ODF.si_trk_phase_status TRK_PHSTAT, ODF.si_trk_dep_status DEP_STAT, ODF.si_trk_comment TRK_COMM, T.PRCATEGORY PR_CAT FROM (SELECT T.prname, T.prStart, T.prFinish, T.prStatus, odf.si_trk_scope_status, odf.si_trk_res_status, odf.si_trk_budet_status, odf.si_track_phase, odf.si_trk_phase_status, (case when T.prCategory = 'Deployed' then odf.si_org_dep_date else null END)org_dt, (case when T.prCategory ='Deployed' then T.PRFINISH else null END)cur_dt, odf.si_trk_dep_status, odf.si_trk_comment, T.prcategory, T.prid milestone_int_id, inv.id project_int_id FROM prtask T inner join odf_ca_task odf on odf.id = t.PRid inner join prtask N on N.PRid= odf.id INNER JOIN inv_investments inv ON t.prprojectid = inv.id INNER JOIN inv_investments invn ON N.prprojectid = invn.id AND odf.si_status_rpt_flag = 1) AND INV.ID = ? AND 1=? and 1=1 .

Outcomes