Customer Portal

Error in extern transformation in EXT_MERGE_JOIN

  1. CloverDX Customer Portal
  2. Forum
  3. Error in extern transformation in EXT_MERGE_JOIN
Avatar
wmdbowler
  • Answered
Follow
Trying to use a left outer join to join multiple files but having trouble just getting two files to join receiving this error (cloverETL 2.4.3):

ERROR [WatchDog] - Node EXT_MERGE_JOIN0 finished with status: Error occurred in nested transformation: ERROR caused
by: Record number: 0, field number: 0. Message: Error in extern transformation class TransformTransformEXT_MERGE_J
OIN0: null
DEBUG [WatchDog] - Node EXT_MERGE_JOIN0 error details:
org.jetel.exception.TransformException: Record number: 0, field number: 0. Message: Error in extern transformation
class TransformTransformEXT_MERGE_JOIN0: null
at TransformTransformEXT_MERGE_JOIN0.transform(TransformTransformEXT_MERGE_JOIN0.java:48)
at org.jetel.component.MergeJoin.flushMin(MergeJoin.java:310)
at org.jetel.component.MergeJoin.execute(MergeJoin.java:347)
at org.jetel.graph.Node.run(Node.java:371)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
at java.lang.Thread.run(Thread.java:595)

Here is my graph:
My first attempts were without a sort but the data was extracted sorted so it shouldn't require sorting. I added a sort to the two files to see if that would resolve the issue but it did not. If I use the default of inner join the EXT_MERGE_JOIN works just fine. Help please!

<?xml version="1.0" encoding="UTF-8"?>
<Graph>
<Global>
<Metadata id="Metadata1">
<Record name="ALL_DataDelimited" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" type="long"/>
<Field delimiter="|" name="EMPI_ID" nullable="true" type="string"/>
<Field delimiter="|" name="Bedford" nullable="true" type="string"/>
<Field delimiter="|" name="Best" nullable="true" type="string"/>
<Field delimiter="|" name="Childrens" nullable="true" type="string"/>
<Field delimiter="|" name="Epic" nullable="true" type="string"/>
<Field delimiter="|" name="Healthplan" nullable="true" type="string"/>
<Field delimiter="|" name="Magee" nullable="true" type="string"/>
<Field delimiter="|" name="Medipac" nullable="true" type="string"/>
<Field delimiter="|" name="NW" nullable="true" type="string"/>
<Field delimiter="|" name="Passavant" nullable="true" type="string"/>
<Field delimiter="|" name="Mercy" nullable="true" type="string"/>
<Field delimiter="|" name="LastName" nullable="true" type="string"/>
<Field delimiter="|" name="FirstName" nullable="true" type="string"/>
<Field delimiter="|" name="State" nullable="true" type="string"/>
<Field delimiter="|" name="ZipCode" nullable="true" type="string"/>
<Field delimiter="|" name="SSN" nullable="true" type="string"/>
<Field delimiter="|" name="DOB" nullable="true" type="string"/>
<Field delimiter="|" name="Sex" nullable="true" type="string"/>
<Field delimiter="|" name="Phone" nullable="true" type="string"/>
<Field delimiter="|" name="mpac_mrn" nullable="true" type="string"/>
<Field delimiter="|" name="wpic_mrn" nullable="true" type="string"/>
<Field delimiter="|" name="nw_mrn" nullable="true" type="string"/>
<Field delimiter="\n" name="Expired" nullable="true" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata0">
<Record name="GLOBAL_EXTRACT" recordSize="-1" type="fixed">
<Field name="EMPI_ID" nullable="true" size="12" type="string"/>
<Field name="MEDIPAC_CPI" nullable="true" size="9" type="string"/>
<Field name="SPACE" nullable="true" size="1" type="string"/>
<Field name="MEDIPAC_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE1" nullable="true" size="1" type="string"/>
<Field name="EPIC_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE2" nullable="true" size="1" type="string"/>
<Field name="WPIC_CPI" nullable="true" size="9" type="string"/>
<Field name="SPACE3" nullable="true" size="1" type="string"/>
<Field name="WPIC_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE4" nullable="true" size="1" type="string"/>
<Field name="TDS_MRN" nullable="true" size="9" type="string"/>
<Field name="HP_ID" nullable="true" size="11" type="string"/>
<Field name="SPACE5" nullable="true" size="1" type="string"/>
<Field name="MAGEE_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE6" nullable="true" size="1" type="string"/>
<Field name="LASTNAME" nullable="true" size="25" type="string"/>
<Field name="FIRSTNAME" nullable="true" size="25" type="string"/>
<Field name="DOB" nullable="true" size="10" type="string"/>
<Field name="SEX" nullable="true" size="1" type="string"/>
<Field name="SSN" nullable="true" size="11" type="string"/>
<Field name="STATE" nullable="true" size="2" type="string"/>
<Field name="SPACE7" nullable="true" size="10" type="string"/>
<Field name="ZIPCODE" nullable="true" size="10" type="string"/>
<Field name="EXPIRED" nullable="true" size="1" type="string"/>
<Field name="SPACE8" nullable="true" size="1" type="string"/>
<Field name="CHILDERNS_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE9" nullable="true" size="3" type="string"/>
<Field name="PASSAVANT_MRN" nullable="true" size="9" type="string"/>
<Field name="SPACE10" nullable="true" size="3" type="string"/>
<Field delimiter="\n" name="BEST_ID" nullable="true" size="10" type="string"/>
</Record>
</Metadata>
<Metadata id="memaddr">
<Record name="memaddr" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="stline1" nullable="true" shift="0" type="string"/>
<Field delimiter="|" name="stline2" nullable="true" shift="0" type="string"/>
<Field delimiter="|" name="stline3" nullable="true" shift="0" type="string"/>
<Field delimiter="|" name="stline4" nullable="true" shift="0" type="string"/>
<Field delimiter="|" name="city" nullable="true" shift="0" type="string"/>
<Field delimiter="|" name="state" nullable="true" shift="0" type="string"/>
<Field delimiter="\n" name="zipcode" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="memattr">
<Record name="memattr" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="\n" name="attrval" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="memdate">
<Record name="memdate" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="\n" name="dateval" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="memhead">
<Record name="memhead" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="\n" name="memidnum" nullable="true" type="string"/>
</Record>
</Metadata>
<Metadata id="memident">
<Record name="memident" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="\n" name="idnumber" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="memname">
<Record name="memname" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="onmlast" nullable="true" shift="0" type="string"/>
<Field delimiter="\n" name="onmfirst" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="memphone">
<Record name="memphone" recordSize="-1" type="delimited">
<Field delimiter="|" name="curentrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="caudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="|" name="maudrecno" nullable="true" shift="0" type="long"/>
<Field delimiter="\n" name="phnumber" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
</Global>
<Phase number="1">
<Node enabled="enabled" fileURL="ext_enthead_best.unl" guiHeight="0" guiName="Delimited Data Reader" guiWidth="0" guiX="139" guiY="867" id="DELIMITED_BEST_READER" type="DELIMITED_DATA_READER"/>
<Node enabled="enabled" fileURL="ext_entempiid.unl" guiHeight="0" guiName="Delimited Data Reader" guiWidth="0" guiX="140" guiY="690" id="DELIMITED_EMPIID_READER" type="DELIMITED_DATA_READER"/>
<Node enabled="enabled" guiHeight="0" guiName="Ext Sort" guiWidth="0" guiX="386" guiY="795" id="EXT_BEST_SORT" sortKey="curentrecno(a)" type="EXT_SORT"/>
<Node enabled="enabled" guiHeight="0" guiName="Ext Sort" guiWidth="0" guiX="383" guiY="648" id="EXT_EMPIID_SORT" sortKey="curentrecno(a)" type="EXT_SORT"/>
<Edge fromNode="DELIMITED_BEST_READER:0" guiBendpoints="" id="Edge7" inPort="Port 0 (in)" metadata="memhead" outPort="Port 0 (out)" toNode="EXT_BEST_SORT:0"/>
<Edge fromNode="DELIMITED_EMPIID_READER:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="memattr" outPort="Port 0 (out)" toNode="EXT_EMPIID_SORT:0"/>
<Edge fromNode="EXT_BEST_SORT:0" guiBendpoints="" id="Edge3" inPort="Port 1 (slave)" metadata="memhead" outPort="Port 0 (out)" toNode="EXT_MERGE_JOIN0:1"/>
<Edge fromNode="EXT_EMPIID_SORT:0" guiBendpoints="" id="Edge2" inPort="Port 0 (driver)" metadata="memattr" outPort="Port 0 (out)" toNode="EXT_MERGE_JOIN0:0"/>
</Phase>
<Phase number="2">
<Node enabled="enabled" guiHeight="0" guiName="Ext Merge Join" guiWidth="0" guiX="547" guiY="761" id="EXT_MERGE_JOIN0" joinKey="curentrecno" joinType="leftOuter" type="EXT_MERGE_JOIN">
<attr name="transform"><![CDATA[${out.0.curentrecno} = ${in.0.curentrecno};
${out.0.EMPI_ID} = ${in.0.attrval};
${out.0.Best} = ${in.1.memidnum};
]]></attr>
</Node>
<Edge fromNode="EXT_MERGE_JOIN0:0" guiBendpoints="" id="Edge63" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="EXT_SORT0:0"/>
</Phase>
<Phase number="3">
<Node enabled="enabled" guiHeight="0" guiName="Ext Sort" guiWidth="0" guiX="774" guiY="770" id="EXT_SORT0" sortKey="EMPI_ID(a)" type="EXT_SORT"/>
<Edge fromNode="EXT_SORT0:0" guiBendpoints="" id="Edge64" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="DELIMITED_DATA_WRITER0:0"/>
</Phase>
<Phase number="4">
<Node append="false" enabled="enabled" fileURL="person_global.txt" guiHeight="0" guiName="Delimited Data Writer" guiWidth="0" guiX="777" guiY="857" id="DELIMITED_DATA_WRITER0" type="DELIMITED_DATA_WRITER"/>
</Phase>
</Graph>

Comments 2

Sort by Date Votes
  • Avatar
    avackova
    0
    Comment actions Permalink
    Hi,
    problem is, that when you do left outer join transformation gets null on the 1st input instead of DataRecord, so in the line 
    ${out.0.EMPI_ID} = ${in.0.attrval};
    NullPointerException occurs. There is no possiblility of checking this in TLLite, so you can surround it by try-catch block:
    ${out.0.curentrecno} = ${in.0.curentrecno}; 
    ${out.0.EMPI_ID} = ${in.0.attrval};
    try{
       ${out.0.Best} = ${in.1.memidnum}; 
    }catch(Exception e){
       ${out.0.Best} = -1;
    }
  • Avatar
    wmdbowler
    0
    Comment actions Permalink
    Thanks for the advice. I didn't realize I'd have to put in that handling. All fixed now.

Please sign in to leave a comment.

Didn't find what you were looking for?

New post