Recently I tried to customize a standard query using “For XML PATH (‘’)” by overriding it on ASTC (custom) layer. But I got error when the query was run by the application. Asked R&D team and found out it is caused by third party parser which can’t parse “For XML PATH (‘’)” on ASTC layer. But adding “For XML PATH (‘’)” in Astea layer is fine. The solution is to change the standard query in Astea layer directly or use alternative of “For XML PATH (‘’)” .
overridden query:
Select order_line.cust_company_id cust_company_id, order_line.line_no line_no,
order_line.order_id order_id, order_line.request_id request_id,
order_line.ldmnd_stat_id ldmnd_stat_id, ldmnd_stat.descr#LANG material_status,
order_line.open_date open_date, order_line.creation_datetime
creation_datetime, order_line.priority priority, service_call.sa_person_id
sa_person_id, order_stat.descr#LANG order_stat_descr, order_line.descr descr,
address.address_id address_id, service_call_env.eta_date eta_date,
service_call_env.sla_eta_clock sla_eta_clock,
service_call_env.sla_recall_clock sla_recall_clock,
service_call_env.sla_resolve_clock sla_resolve_clock,
order_line.cust_company_descr cust_company_descr, service_call_env.eta_time
eta_time, order_line.last_stop_code_id last_stop_code_id,
order_line.site_company_id site_company_id, order_line.site_company_descr
site_company_descr, order_line.order_actvitity_status order_actvitity_status,
activity_status.descr#LANG activity_status_descr, service_call.severity_id
severity_id, order_line.caller_name caller_name, service_call.actgr_id
actgr_id, actgr.descr#LANG actgr_descr, person.search_name search_name,
service_call.pcode_id pcode_id, service_call.refno refno, order_line.cconth_id
cconth_id, order_line.phone_id phone_id, order_line.currency_id currency_id,
service_call.callt_id callt_id, service_call.serial_no serial_no,
service_call.tagno tagno, service_call.bpart_id bpart_id, address.address_1
address_1, service_call.fcoh_id fcoh_id, service_call.call_source_id
call_source_id, order_line.order_stat_uniq_id order_stat_uniq_id,
order_stat.order_type_id order_stat_order_type_id, order_line.node_id node_id,
order_line.alt_contact alt_contact, order_line.created_by As created_by,
node.descr#LANG As node_descr, itil_category.descr#LANG As
itil_category_descr, service_call.impact_code_id As impact_code_id,
service_call.cause_descr As cause_descr, service_call.is_workaround_supplied
As is_workaround_supplied, service_call.itil_category_id As itil_category_id,
service_call.itil_status As itil_status, service_call.itil_sub_category_id As
itil_sub_category_id, service_call.itil_type As itil_type,
service_call.num_of_users As num_of_users, service_call.owner_id As owner_id,
service_call.urgency_code_id As urgency_code_id,
SubString(service_call.workaround_descr, 1, 60) As workaround_descr,
service_call.workaround_descr As long_workaround, owner_descr_tbl.search_name
As owner_descr, order_line.chat_session_id As chat_session_id,
service_call.percent_completed As percent_completed,
service_call.units_completed As units_completed, service_call.vendor_cconth_id
As vendor_cconth_id, itil_category_lines.descr#LANG As
itil_category_lines_descr, itil_category_lines.itil_sub_category_code As
itil_sub_category_code, service_call.change_category As change_category,
service_call.change_status As change_status, service_call.ext_product_descr As
ext_product_descr, service_call.ext_product_id As ext_product_id,
service_call.ext_serial_no As ext_serial_no, order_line.open_time As
open_time, pcode.descr As pcode_descr, service_call.contr_eta As contr_eta,
service_call.contr_resolve As contr_resolve,
SubString(order_line.problem_desc, 1, 60) As problem_desc,
order_line.problem_desc As long_desc, service_call.is_maintenance As
is_maintenance, service_call.contr_recall As contr_recall,
service_call.is_external As is_external, service_call_env.escal_level As
escal_level, service_call_env.escal_date As escal_date,
service_call.equipment_status_code_id As equipment_status_code_id, order_line.po_id po_id,a1.city cust_sold_to_city,a1.state_prov_id cust_sold_to_state,a1.zip cust_sold_to_zip,a2.city site_city,a2.state_prov_id site_state ,a2.zip site_zip,
order_line.cst_order_reason As cst_order_reason,order_line.cst_service_order_nte As cst_service_order_nte,
STUFF((select ';' + order_xref.table_key
FROM order_xref
where order_xref.order_id = order_line.order_id And
order_xref.table_name = 'cause'
FOR XML PATH ('')), 1, 1, '') as cause_id
From order_line With(NoLock) Left Outer Join
(service_call With(NoLock) Left Outer Join
actgr With(NoLock) On service_call.actgr_id = actgr.actgr_id Left Outer Join
person With(NoLock) On service_call.sa_person_id = person.person_id) On
order_line.order_id = service_call.order_id Left Outer Join
order_stat With(NoLock) On (order_line.order_stat_uniq_id =
order_stat.order_stat_uniq_id) And (order_line.order_type_id =
order_stat.order_type_id) And (order_stat.order_type_id = 'service_order')
Left Outer Join
address With(NoLock) On order_line.ship_to_address_id = address.address_id
left outer join address_xref ax1 on order_line.order_id = ax1.table_key and
ax1.address_type_id = 'SoldTo' and ax1.table_name = 'order_line'
Left Outer Join
address a1 With(NoLock) On ax1.address_id = a1.address_id
left outer join address_xref ax2 With(NoLock) on order_line.order_id = ax2.table_key and
ax2.address_type_id = 'ShipTo' and ax2.table_name = 'order_line'
Left Outer Join address a2 With(NoLock) On ax2.address_id = a2.address_id
Left Outer Join
service_call_env With(NoLock) On order_line.order_id =
service_call_env.order_id Left Outer Join
activity_status With(NoLock) On order_line.order_actvitity_status =
activity_status.activity_status Left Join
node With(NoLock) On order_line.node_id = node.node_id Left Outer Join
itil_category With(NoLock) On service_call.itil_category_id =
itil_category.itil_category_id Left Outer Join
person owner_descr_tbl On service_call.owner_id = owner_descr_tbl.person_id
Left Outer Join
itil_category_lines With(NoLock) On service_call.itil_sub_category_id =
itil_category_lines.itil_sub_category_id Left Outer Join
pcode On service_call.pcode_id = pcode.pcode_id Left Outer Join
callt On service_call.callt_id = callt.callt_id Left Outer Join
ldmnd_stat With(NoLock) On order_line.ldmnd_stat_id = ldmnd_stat.ldmnd_stat_id
Where ((order_line.cust_company_id = @a_company_id) Or
(order_line.site_company_id = @a_site_id) Or ('a_cust_portal_cr' =
@a_cust_portal_cr)) And (order_line.order_stat_uniq_id >= 0) And
(order_line.order_stat_uniq_id < 700) And ('where_cond' = @where_cond) And
(order_line.order_type_id = 'service_order') And ('a_search_name' =
@a_search_name) And ('in_arg' = @in_arg) And ('a_ldmnd_stat_id' =
@a_ldmnd_stat_id) And ('a_stop_code_id' = @a_stop_code_id) And
('a_actgr_id' = @a_actgr_id) And ('a_vend_portal_cr' = @a_vend_portal_cr) And
('a_vendor_id' = @a_vendor_id) And ('a_fco_serv_bull_arg' =
@a_fco_serv_bull_arg) And ('a_order_source_arg' = @a_order_source_arg) And
('a_is_maintenance' = @a_is_maintenance) And ('a_problem_code' =
@a_problem_code) And ('where_cond2' = @where_cond2)
And ('a_orig_doc_id' = @a_orig_doc_id)
And ('a_cst_sap_po' = @a_cst_sap_po)
And ('a_cst_sap_requisition' = @a_cst_sap_requisition)
And ('a_cause_code' = @a_cause_code)
And ('a_repair_code' = @a_repair_code)
event log error:
Message body:
Error in DataViewMgr.Retrieve
Invalid SELECT statement.
Unexpected token "FOR" at line 60, pos 7
