Clarity

We have process with a step that includes a gel script that is supposed to:

1. Copy the % value from the custom % Complete field on the Status report object to the % Complete field on the Project object.

2. Copy the date from the OOTB Report Date field on the Status Report object to the As of Date field on the Project Object.

When we run the process, it errors at this gel script.  We can't seem to figure out what this issue is.

Here's the Gel Script:

<gel:script xmlns:core="jelly:core" xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:sql="jelly:sql"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<core:set value="${gel_thisstatus}" var="objID"/>
<gel:log level="info" message="Object ID: ${objID}"/>
<gel:setDataSource dbId="niku"/>
<sql:update var="results">

UPDATE INV_PROJECTS

SET PERCENT_COMPLETE =
(select msu_sr_per_complete
FROM odf_ca_cop_prj_statusrpt
where id = ${objID}),

prAsOf =
(select cop_report_date
FROM odf_ca_cop_prj_statusrpt
where id = ${objID})

where id =
(select odf_parent_id
FROM odf_ca_cop_prj_statusrpt
where id = ${objID})

</sql:update>
</gel:script>

1. Direct database updates (especially to core tables like INV_PROJECTS) are not supported and are prone to cause problems. You should always update core tables through supported means (i.e. XOG).

2. Your update statement says;

UPDATE INV_PROJECTS
SET PERCENT_COMPLETE = (select msu_sr_per_complete FROM odf_ca_cop_prj_statusrpt where id = 5061043)
, prAsOf = (select cop_report_date FROM odf_ca_cop_prj_statusrpt where id = 5061043)
where id = (select odf_parent_id FROM odf_ca_cop_prj_statusrpt where id = 5061043)

and id is not a column on INV_PROJECTS - hence the ORA-00904: "ID": invalid identifier error.

Thanks David.  I keep getting these workflows from a Vendor and they decide to do a direct database update instead of through a XOG.

^ if I were receiving these workflows from a vendor, I might choose to challenge their suitability for use. Certainly the not-supported-by-CA nature of them (so I hope the vendor includes support) before we even get into the syntax error (so was this even tested?). 

Hi,

As David mentioned that id is not a column on INV_PROJECTS

I think it should be "prid" on INV_PROJECTS.

Regards,

Shoichi

Thanks Shochi!  I will try this.  

Please consider if you are doing the correct thing.  Other forum members are indicating this is an unsupported, so why aren't you updating the data via XOG, which is the supported way?

Just because the SQL Update comes back saying one record updated (or similar), you may still experience issues.  For example, the custom attribute on Status Report msu_sr_per_complete may be storing data as numeric and numbers from 0 to 100, where as PERCENT_COMPLETE is being stored as decimal (ie 0 to 1).

Use XOG to update the data.  It will then warn you if the attribute can't be updated (eg Project is not set to Manual for % Complete Calculation) or setting the value greater than 1 (aka 100%).