AnsweredAssumed Answered

Process with GEL Script

Question asked by mtognetti on Feb 15, 2018
Latest reply on Feb 15, 2018 by Dave_3.0

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

Outcomes