Annealed Query with Gel script

[Daniel Pesantes]

Hello,

I am trying to perform a nested query with gel script but it gives me the following error:

[CAS] Error during script execution: org.apache.commons.jelly.JellyTagException: null: 33: 30: <sql: query> SELECT pr. prresourceid resourceid FROM PRTeam pr where pr.prprojectid =: The column index is out of range: 1, number of columns: 0.

I put the script that I am using and I hope someone can help me and indicate where I am wrong, thanks.

Script:

<core:catch var="erro">
<sql:query dataSource="${nikuDS}" escapeText="false" var="rs_ideia"><![CDATA[SELECT
inv_ida.code,
inv_ida.name,
ida.partition_code
FROM
inv_investments inv_ida
JOIN odf_ca_idea ida
ON inv_ida.id = ida.id
WHERE
inv_ida.id = ?::bigint]]>
<sql:param value="${gel_objectInstanceId}"/></sql:query>
<sql:query dataSource="${nikuDS}" escapeText="false" var="rs_dwh_value"><![CDATA[SELECT
RespImpact.dwh_value id_aplicacion
FROM
inv_investments inv_ida
JOIN ODF_MULTI_VALUED_LOOKUPS RespImpact
ON inv_ida.id = RespImpact.pk_id
where RespImpact.attribute = 'pac_app_impac'
and RespImpact.pk_id = ?::bigint]]>
<sql:param value="${gel_objectInstanceId}"/></sql:query>
<core:forEach items="${rs_dwh_value.rows}" var="ap">
<sql:query dataSource="${nikuDS}" escapeText="false" var="rs_resources"><![CDATA[SELECT
pr.prresourceid recursoid
FROM
PRTeam pr
where pr.prprojectid = ${ap.id_aplicacion}]]>
<sql:param value="${gel_objectInstanceId}"/></sql:query>

<core:if test="${rs_resources.getRowCount() &gt; 0}">
<core:set value="${rs_ideia.rows[0]}" var="ideia"/>
<core:new className="com.niku.union.security.DefaultSecurityIdentifier" var="secId1"/>
<core:invokeStatic className="com.niku.union.security.UserSessionControllerFactory" method="getInstance" var="UserSessionCtrl"/>
<core:invoke method="init" on="${UserSessionCtrl}" var="secId3">
<core:arg type="java.lang.String" value="${xog_user}"/>
<core:arg type="com.niku.union.security.DefaultSecurityIdentifier" value="${secId1}"/>
</core:invoke>
<core:set value="${secId3.getSessionId()}" var="sessionID"/>
<gel:parse var="xog">
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_customObjectInstance.xsd">
<Header action="write" externalSource="NIKU" objectType="idea" version="15"/>
<Ideas>
<Idea name="${ideia.name}" objectID="${ideia.code}">
<CustomInformation>
<ColumnValue name="partition_code">${ideia.partition_code}</ColumnValue>
<ColumnValue name="pac_resp_app_impac">
<core:forEach items="${rs_resources.rows}" var="resource">
<Value>${resource.recursoid}</Value>
</core:forEach>
</ColumnValue>
</CustomInformation>
</Idea>
</Ideas>
</NikuDataBus>
</gel:parse>
<soap:invoke endpoint="internal" var="result">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<gel:include select="$xog"/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
<gel:set asString="true" select="$result/soapenv:Envelope/soapenv:Body/XOGOutput/Status/@state" var="xog_status_state"/>
<gel:set asString="true" select="$result/soapenv:Envelope/soapenv:Body/XOGOutput/Statistics/@failureRecords" var="xog_failure_records"/>
<core:if test="${(xog_status_state == 'FAILURE') || (xog_failure_records &gt; 0)}">
<gel:log level="debug">[CAS] XOG que foi executado.</gel:log>
<gel:log level="debug">
<gel:expr select="$xog"/>
</gel:log>
<gel:log level="debug">[CAS] Resultado do XOG que foi executado.</gel:log>
<gel:log level="debug">
<gel:expr select="$result"/>
</gel:log>
<gel:log level="error">[CAS] Erro na execução do xog.</gel:log>
</core:if>
<soap:invoke endpoint="internal" var="logout_session">
<soap:message>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xog="http://www.niku.com/xog">
<soapenv:Header>
<xog:Auth>
<xog:SessionID>${sessionID}</xog:SessionID>
</xog:Auth>
</soapenv:Header>
<soapenv:Body>
<xog:Logout/>
</soapenv:Body>
</soapenv:Envelope>
</soap:message>
</soap:invoke>
</core:if>
</core:forEach>
</core:catch>

[Karl Shlayfman]

H Daniel, I would suggest you might need to introduce some logging in here so you can see your parameters and other data points.
For example you have this query which it seems where it is erroring out
CDATA[SELECT
pr.prresourceid recursoid
FROM
PRTeam pr
where pr.prprojectid = ${ap.id_aplicacion}]]>
One of the reasons perhaps is that ${ap.id_aplicacion} which parameter you passing might not be resolving properly, so maybe you should try to output the value first to ensure it is what is expected.

Regards,
Karl