I am having a problem with how to deal with nulls in the transformation language. I have 3 fields from a source system( first_name, last_name, name_suffix ) that I need to use to create three other fields for a data warehouse( first_last_name, last_first_name, combined_name ). The problem is that any of first three can be null but the transformation language seems to treat “” as null. The easiest is first_last_name, it should take the form of first_namelast_namename_suffix. If any are null the between it and the next should not be there. Here is the transformation code I would like to have handle it:
$NameMetadata.first_last_name := iif(isnull($NameMetadata.FIRST_NAME), “” , $NameMetadata.FIRST_NAME + " ") + nvl($NameMetadata.LAST_NAME, “”) + iif(isnull($NameMetadata.NAME_SUFFIX), “” , " " + $NameMetadata.NAME_SUFFIX);
The error I get is:
DEBUG [WatchDog] - Node Transformation error details:
org.jetel.interpreter.TransformLangExecutorRuntimeException: Interpreter runtime exception on line 9 column 99 : add - NULL value not allowed
arg[0] null “!! NULL !!”
arg[1] java.lang.StringBuilder “Thoman”
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:397)
at org.jetel.interpreter.node.CLVFAddNode.jjtAccept(CLVFAddNode.java:19)
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:390)
at org.jetel.interpreter.node.CLVFAddNode.jjtAccept(CLVFAddNode.java:19)
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:1632)
at org.jetel.interpreter.node.CLVFMapping.jjtAccept(CLVFMapping.java:32)
at org.jetel.interpreter.TransformLangExecutor.executeFunction(TransformLangExecutor.java:1729)
at org.jetel.component.WrapperTL.executePreparedFunction(WrapperTL.java:325)
at org.jetel.component.RecordTransformTL.transform(RecordTransformTL.java:91)
at org.jetel.component.Reformat.execute(Reformat.java:191)
at org.jetel.graph.Node.run(Node.java:364)
at java.lang.Thread.run(Unknown Source)
Is this a problem inherent in Java or something in the Clover Engine?
is there an estimate for when the next release will be out, even if it is a beta I would like to know how soon?
i switch to CloverETL version 2.1.3 and tested both the nvl(arg, default) and the iff(condition, falseValue, trueValue) functions and both still have problems with having “” as one of the arguments
here is the error:
ERROR [WatchDog] - Node Transformation finished with status: ERROR caused by: Interpreter runtime exception on line 9 column 164 : add - NULL value not allowed
arg[0] null “!! NULL !!”
arg[1] java.lang.StringBuilder “AllenTEST”
DEBUG [WatchDog] - Node Transformation error details:
org.jetel.interpreter.TransformLangExecutorRuntimeException: Interpreter runtime exception on line 9 column 164 : add - NULL value not allowed
arg[0] null “!! NULL !!”
arg[1] java.lang.StringBuilder “AllenTEST”
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:397)
at org.jetel.interpreter.node.CLVFAddNode.jjtAccept(CLVFAddNode.java:19)
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:390)
at org.jetel.interpreter.node.CLVFAddNode.jjtAccept(CLVFAddNode.java:19)
at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:1634)
at org.jetel.interpreter.node.CLVFMapping.jjtAccept(CLVFMapping.java:32)
at org.jetel.interpreter.TransformLangExecutor.executeFunction(TransformLangExecutor.java:1731)
at org.jetel.component.WrapperTL.executePreparedFunction(WrapperTL.java:325)
at org.jetel.component.RecordTransformTL.transform(RecordTransformTL.java:91)
at org.jetel.component.Reformat.execute(Reformat.java:191)
at org.jetel.graph.Node.run(Node.java:364)
at java.lang.Thread.run(Unknown Source)
Please, try it in latest release 2.1.3 and let me know if described issue persists. Then we could do our inner tests based on your description.
Best regards, Martin.
New release will be next week probably (at least beta version)
I think you solved your issue in proper way. Can I ask you what engine version are you using? Inner transformation language now coming through big modification…
Martin
I am using clover version 2.1.2 I did not update to 2.1.3 because the patch notes did not seem to apply to what i was working on since i dont have null numbers or use the switch statement. Is your fix in 2.1.3 or in the next update?
Hi,
the problem was found and fixed. Correct version is in svn and will be included in next release.
Agata
Hi,
this should work as you need:
function fullName(firstName,lastName,suffix){
string tmp = “”;
string result;
if (not isnull(firstName) and not isnull(lastName)) tmp=" “;
result = concat(s1,tmp,s2);
tmp = “”;
if (not isnull(result) and not isnull(suffix)) tmp=” ";
result=concat(result,tmp,s3);
return result
}
Agata