AnsweredAssumed Answered

NPT-0120: There was an error while handling your request. Make sure your NSQL/SQL syntax is correct. Note that the : and @ delimiter characters used in expressions must be escaped with \.

Question asked by Rnitram on Aug 13, 2014
Latest reply on Mar 17, 2015 by Owen_R

Hi,

 

I am trying to create the query below. Unfortunately I receive the error message:

NPT-0120: There was an error while handling your request. Make sure your

NSQL/SQL syntax is correct. Note that the : and @ delimiter characters used in

expressions must be escaped with \.

 

However - to me - the SQL looks fine and I can execute it in SQL-Developer. Any ideas are welcome:

select
@select:dim:user_def:implied:del:SHM.TSK_ID:TASK_ID@,
@select:dim_prop:user_def:implied:del:SHM.TSK_NAME:TASK_NAME@,
@select:dim_prop:user_def:implied:del:SHM.PRJ_CLASS:PRJ_CLASS@,
@select:dim_prop:user_def:implied:del:SHM.DEL_TYPE:DEL_TYPE@,
@select:dim_prop:user_def:implied:del:SHM.SH_ID:SH_ID@,
@select:dim_prop:user_def:implied:del:SHM.SH_NAME:SH_NAME@

FROM
(select task_1.prid tsk_id, task_1.prname tsk_name, ocp_1.a1_project_class prj_class, oct_1.a1_deliverable_type del_type, omvl_1.value sh_id, sr_1.full_name sh_name
FROM niku.inv_investments inv_1
JOIN niku.odf_ca_project ocp_1 ON inv_1.id=ocp_1.id AND inv_1.is_active=1
JOIN niku.inv_projects ip_1 ON inv_1.id=ip_1.prid AND ip_1.is_template=1
JOIN niku.prtask task_1 ON inv_1.id=task_1.prprojectid
JOIN niku.odf_ca_task oct_1 ON task_1.prid=oct_1.id AND oct_1.a1_is_deliverable=1
JOIN niku.odf_multi_valued_lookups omvl_1 ON omvl_1.attribute ='a1_del_resp_roles' and omvl_1.object='task' and omvl_1.pk_id=task_1.prid
JOIN niku.srm_resources sr_1 ON omvl_1.value=sr_1.id
JOIN
(
select ocp_2.obj_request_category, ocp_2.obj_request_type, ocp_2.a1_project_class, oct_2.a1_deliverable_type, omvl_2.value, sr_2.full_name
FROM niku.inv_investments inv_2
JOIN niku.odf_ca_project ocp_2 ON inv_2.id=ocp_2.id AND inv_2.is_active=1
JOIN niku.inv_projects ip_2 ON inv_2.id=ip_2.prid AND ip_2.is_template=1
JOIN niku.prtask task_2 ON inv_2.id=task_2.prprojectid
JOIN niku.odf_ca_task oct_2 ON task_2.prid=oct_2.id AND oct_2.a1_is_deliverable=1
JOIN niku.odf_multi_valued_lookups omvl_2 ON omvl_2.attribute ='a1_del_resp_roles' and omvl_2.object='task' and omvl_2.pk_id=task_2.prid
JOIN niku.srm_resources sr_2 ON omvl_2.value=sr_2.id

WHERE
ocp_2.obj_request_type=(select ocp1.obj_request_type FROM niku.odf_ca_project ocp1 WHERE ocp1.id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
and ocp_2.a1_project_class=(select CASE WHEN ocp2.a1_project_class='Master Project' THEN 'Normal Project' ELSE ocp2.a1_project_class END FROM niku.odf_ca_project ocp2 WHERE ocp2.id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
and ocp_2.obj_request_category=(select ocp3.obj_request_category FROM niku.odf_ca_project ocp3 WHERE ocp3.id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@)
EXCEPT
select ocp_3.obj_request_category, ocp_3.obj_request_type, ocp_3.a1_project_class, oct_3.a1_deliverable_type, omvl_3.value, sr_3.full_name
FROM niku.inv_investments inv_3
JOIN niku.odf_ca_project ocp_3 ON inv_3.id=ocp_3.id AND inv_3.is_active=1
JOIN niku.inv_projects ip_3 ON inv_3.id=ip_3.prid AND ip_3.is_template=0
JOIN niku.prtask task_3 ON inv_3.id=task_3.prprojectid
JOIN niku.odf_ca_task oct_3 ON task_3.prid=oct_3.id AND oct_3.a1_is_deliverable=1
JOIN niku.odf_multi_valued_lookups omvl_3 ON omvl_3.attribute ='a1_del_resp_roles' and omvl_3.object='task' and omvl_3.pk_id=task_3.prid
JOIN niku.srm_resources sr_3 ON omvl_3.value=sr_3.id
and inv_3.id=@WHERE:PARAM:XML:INTEGER:/data/id/@value@
) SHM_0 ON oct_1.a1_deliverable_type=SHM_0.a1_deliverable_type
AND ocp_1.obj_request_type=SHM_0.obj_request_type
AND ocp_1.a1_project_class=SHM_0.a1_project_class
AND ocp_1.obj_request_category=SHM_0.obj_request_category
AND omvl_1.value=SHM_0.value) SHM

WHERE @FILTER@


Outcomes