In order to "get around" the limitation of the "<" or ">" characters in GEL, I modified the query to use "NOT BETWEEN low AND high"
select distinct E1.divid, E1.sdivid from
(select count(C1.divid) Duplicate, C1.sdivid from
(select distinct O1.divid, O1.sdivid from trg_dep_obs_stage O1) C1 group by C1.sdivid) D1,
trg_dep_obs_stage E1
where D1.sdivid = E1.sdivid
and D1.duplicate NOT BETWEEN 0 AND 1
This is a clumsy workaround. Does anyone have ideas on why ">" or "<" would cause such heartburn with the !@#$ Clarity GEL???????
<gel:script xmlns:core="jelly:core"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<!--Parameters section-->
<!--Not needed:-->
<gel:parameter default="True" var="PUseToday"/>
<gel:parameter default="whatever" var="PFileName"/>
<gel:parameter default="PS_Div_Cost_Center" var="PFileType"/>
<gel:parameter default="./importfiles" var="PLocalDirectory"/>
<gel:log level="INFO">Modifying Duplicates in trg_dep_obs_stage</gel:log>
<!--Don't forget to thank the source systems for all the problems duplicates create-->
<!--First Find Duplicates-->
<gel:setDataSource dbId="Niku"/>
<!--First Fix duplicates SDIVID in Separate parents-->
<!--Step1: get counts of duplicate records under separate parents-->
<sql:query var="sdivid_dup1">
select distinct E1.divid, E1.sdivid from
(select count(C1.divid) Duplicate, C1.sdivid from
(select distinct O1.divid, O1.sdivid from trg_dep_obs_stage O1) C1 group by C1.sdivid) D1,
trg_dep_obs_stage E1
where D1.sdivid = E1.sdivid
and D1.duplicate > 1
</sql:query>
<gel:log>sdivid_dup1 Row count : ${sdivid_dup1.rowCount}</gel:log>
<!--!:Duplicates more than 0, then loop through the values and update Child IDs with parent ID || Child iD-->
<core:choose>
<core:when test="${sdivid_dup1.rowCount > 0}">
<!--Update "Children ids" for duplicates in different parents-->
<core:forEach begin="0" items="${sdivid_dup1.rows}" trim="true" var="dup1">
<gel:log level="INFO">dup1.divid: ${dup1.divid}</gel:log>
<gel:log level="INFO">dup1.sdivid: ${dup1.sdivid}</gel:log>
<sql:update>
UPDATE (select OS.divid OSDIVID, OS.sdivid OSSDIVID from trg_dep_obs_stage OS
where OS.divid = '${dup1.divid}' and OS.sdivid = '${dup1.sdivid}')
SET OSSDIVID = '${dup1.divid}' || '${dup1.sdivid}'
</sql:update>
</core:forEach>
</core:when>
</core:choose>
<!--Duplicates within grandchild-->
<!--First Fix duplicates SDIVID in Separate parents-->
<!--Step2: get counts of duplicate records under separate parents-->
<sql:query var="sdivid_dup2">
select distinct E1.divid, E1.sdivid, E1.s2divid from
(select count(C1.sdivid) Duplicate, C1.s2divid from (select distinct O1.sdivid, O1.s2divid from trg_dep_obs_stage O1) C1 group by C1.s2divid) D1,
trg_dep_obs_stage E1
where D1.s2divid = E1.s2divid
and D1.duplicate > 1
</sql:query>
<gel:log level="INFO">sdivid_dup2.rowCount: ${sdivid_dup2.rowCount}</gel:log>
<!--!:Duplicates more than 0, then loop through the values and update Child IDs with parent ID || Child iD || Grandchild ID-->
<core:choose>
<core:when test="${sdivid_dup2.rowCount > 0}">
<!--Update "Children ids" for duplicates in different parents-->
<core:forEach begin="0" items="${sdivid_dup2.rows}" trim="true" var="dup2">
<gel:log level="INFO">dup2.divid: ${dup2.divid}</gel:log>
<gel:log level="INFO">dup2.sdivid: ${dup2.sdivid}</gel:log>
<gel:log level="INFO">dup2.sdivid: ${dup2.s2divid}</gel:log>
<!--Update s2divid for every one that has a "duplicate"-->
<sql:update>
UPDATE (select OS.divid OSDIVID, OS.sdivid OSSDIVID, OS.s2divid OS2DIVID from trg_dep_obs_stage OS
where OS.divid = '${dup2.divid}' and OS.sdivid = '${dup2.sdivid}' and OS.s2divid = '${dup2.s2divid}')
SET OS2DIVID = '${dup2.divid}' || '${dup2.sdivid}' || '${dup2.s2divid}'
</sql:update>
</core:forEach>
</core:when>
</core:choose>
<!--Now to fix duplicates within the same parent-->
<!--1: Get a count of Duplicates with same parent-->
<sql:query var="sdivid_duplicates">
select distinct D3.sdivid from
(select count(C3.sdivid) Duplicate, C3.divid, C3.sdivid from
(select distinct O3.divid, O3.sdivid, O3.sdiv from trg_dep_obs_stage O3 order by O3.divid, O3.sdivid) C3
group by C3.divid, C3.sdivid) D3
where
D3.duplicate > 1
</sql:query>
<gel:log level="INFO">sdivid_duplicates: ${sdivid_duplicates.rowCount}</gel:log>
<!--2: Duplicates more than 0, then loop through values - incrementing length of ID until it is unique-->
<core:choose>
<core:when test="${sdivid_duplicates.rowCount > 0}">
<!--Loop through each distinct duplicate to add to the duplicate ID to get a unique value-->
<core:forEach begin="0" items="${sdivid_duplicates.rows}" trim="true" var="dup3">
<!--Each pass this will get a distinct SDIVID and SDIV combination-->
<sql:query var="dup3_dup">
select distinct F3.sdivid, F3.sdiv from trg_dep_obs_stage F3 where F3.sdivid = '${dup3.sdivid}'
</sql:query>
<core:forEach begin="0" items="${dup3_dup.rows}" trim="false" var="dup4">
<gel:log level="INFO">dup4.length.sdivid: ${dup4.length.sdivid}</gel:log>
<gel:log level="INFO">dup4.sdivid: ${dup4.sdivid}</gel:log>
<gel:log level="INFO">dup4.divid: ${dup4.sdiv}</gel:log>
</core:forEach>
<!--Need Loop Here to Pull out "values" from the above query, then loop through, while adding extra characters-->
<!--Once IDs are created uniquely, then write those to the database-->
</core:forEach>
</core:when>
</core:choose>
<!--Update Duplicates-->
<gel:log level="INFO">Finished updating duplicates - make sure and tip your waitress.</gel:log>
<!-- </core:when>
<core:otherwise>-->
<!--Log the exception-->
<!-- <gel:log level="INFO">Error locating or reading file - "${ex}"</gel:log>
</core:otherwise>
</core:choose>-->
</gel:script>