Good Afternoon Community,
I could use some quick help here (an fresh set of eyes). I have a gel script that I have put into a process. The end goal is to get a CSV full of data (5 columns wide). I have not yet limited the number of records as I am still in the testing process and have not yet learned how to introduce a input parameter.
I realize the answer is written within the code and that I just am not seeing it. I can create the output file to the specified directory, but I get no data. The query works as I have tested in in SQL Server Management studio and it returns over 15,000 records in a second or two.
My code:
<gel:script
xmlns:core="jelly:core"
xmlns:email="jelly:email"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:ftp="jelly:com.niku.union.gel.FTPTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:util="jelly:util"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<core:catch var="gException">
<!--This determines the directory where all output files will be saved-->
<gel:parameter var="outputFileBaseDir" default="D:\\mictog\\geltest"/>
<core:new className="java.util.Date" var="currDate"/>
<core:set var="currDate">
<gel:formatDate format="yyyy-MM-dd" dateVar="currDate"/>
</core:set>
<core:set var="outputFile" value="${outputFileBaseDir.concat('.csv')}" />
<gel:log category="User-defined process" level="INFO" message="Running on file ${outputFile}" />
<gel:setDataSource dbId="NIKU"></gel:setDataSource>
<!-- Start writing CSV file-->
<file:writeFile delimiter="," fileName="${outputFile}" >
<gel:log category="User-defined process" level="INFO" message="Writing column headers " />
<file:line>
<file:column value="NoteID"/>
<file:column value="IdeaNbr"/>
<file:column value="IdeaID"/>
<file:column value="DiaryEntry"/>
<file:column value="LatestEntryDate"/>
</file:line>
<!--User Details-->
<sql:query escapeText="false" var="user_query">
<![CDATA[
SELECT
n.id as NOTEID
, i.code
, SRNBR.ph_cherwell_sr_id
, ide.id
, n.diary_entry
, max(n.entry_date) as LatestEntryDate
FROM NIKU.INV_INVESTMENTS i JOIN NIKU.INV_IDEAS IDE
ON i.ID = IDE.ID
LEFT JOIN [niku].[ODF_CA_IDEA_NOTES] N
INNER JOIN (
SELECT
MAX(N.last_updated_date) AS MaxLastUpdateNote
, N.ODF_CNCRT_PARENT_ID
FROM niku.odf_ca_idea_notes N
GROUP BY N.ODF_CNCRT_PARENT_ID
) AS UniqueNote
ON uniquenote.ODF_CNCRT_PARENT_ID = N.odf_parent_id
AND uniquenote.maxLastUpdateNote = N.last_updated_date
ON IDE.id = N.ODF_CNCRT_PARENT_ID
LEFT JOIN NIKU.ODF_CA_IDEA AS SRNbr
ON IDE.id = srnbr.id
WHERE I.ODF_OBJECT_CODE='IDEA'
AND SRNBR.PARTITION_CODE = 'IST'
AND (SRNBR.PH_CHERWELL_SR_ID IS NOT NULL
OR SRNBR.PH_CHERWELL_SR_ID IS NULL)
GROUP BY
N.ID,
, I.CODE
, IDE.ID
, N.DIARY_ENTRY
, SRNBR.PH_CHERWELL_SR_ID
]]>
</sql:query>
<core:forEach items="${user_query.rowsByIndex}" trim="true" var="row">
<file:line>
<core:forEach indexVar="c" begin="0" end="15000" >
<gel:log category="User-defined process" level="info" message="inside file loop " />
<file:column value="${row[c]}"/>
</core:forEach>
</file:line>
</core:forEach>
</file:writeFile>
<gel:log category="User-defined process" level="info" message="End of the process " />
</core:catch>
<core:if test="${!empty(gException)}">
<gel:log>
${gException}
</gel:log>
</core:if>
</gel:script>
I think my problem is in the Core:forEach segment, I feel this is not as well defined (covering the 5 columns) as it should be, but at the same time I do not (yet) have the knowledge.
I appreciate the help.
Thank you.
MT