Hello community,
Could someone help me with a script, I am trying to create a query nested with gel script that I put below:
<gel:script xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:core="jelly:core" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<gel:setDataSource dbId="niku" var="nikuDS"/>
<gel:parameter default="" var="xog_user"/>
<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() > 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 > 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>
<core:if test="${erro != null}">
<gel:log level="error">[CAS] Erro durante execução do script: ${erro}</gel:log>
</core:if>
</gel:script>
But it gives me the following error:
[CAS] Erro durante execução do script: org.apache.commons.jelly.JellyTagException: null:33:30: <sql:query> SELECT pr.prresourceid recursoid FROM PRTeam pr where pr.prprojectid = : The column index is out of range: 1, number of columns: 0.
Does anyone know why this error is due?
I must clarify that the queries do bring me data when testing them from the searches.
Greetings,
Daniel