AnsweredAssumed Answered

GEL Script Import Question:  How do you replace a single quote (')?

Question asked by Lowell on Feb 10, 2011
Latest reply on Feb 26, 2014 by samos2
I have an import running through a GEL script. It imports the values, but my Stored Procedure fails when it encounters: GEN'L CO
This value is problemmatic, because it is the "key" value which I have to match to create an OBS.

For the initial GEL import script, is there a way to "test" for characters and replace them? For instance, Can I replace the single hash mark ' with an underscore?

Transform GEN'L CO
Into GEN_L CO


<core:forEach begin="0" end="0" items="${input.rows}"
trim="false" var="EDate">
<core:set value="${EDate[0]}" var="vExtractDate"/>
</core:forEach>
<core:forEach begin="1" items="${input.rows}" trim="false" var="row">
<core:catch var="ex2">


<sql:update>
INSERT INTO trg_dep_obs_stage
(extract_date, ID, divid, div, sdivid, sdiv, s2divid, s2div, ccid, cc,
load_date, status, effective_date)
VALUES (to_date(?,'yyyy-mm-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,sysdate,?,to_date(?,'yyyy-mm-dd'))
<sql:param value="${vExtractDate}"/>
<sql:param value="${row[0]}"/>
<sql:param value="${row[1]}"/>
<sql:param value="${row[2]}"/>
<sql:param value="${row[3]}"/>
<sql:param value="${row[4]}"/>
<sql:param value="${row[5]}"/>
<sql:param value="${row[6]}"/>
<sql:param value="${row[7]}"/>
<sql:param value="${row[8]}"/>
<sql:param value="${row[9]}"/>
<sql:param value="${row[10]}"/>
</sql:update>
</core:catch>
<gel:out>${ex2}</gel:out>
</core:forEach>

Outcomes