Problem treating "" as null

oldforum

• July 16, 2007 00:00

• Answered

Follow

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_name<Space>last_name<Space>name_suffix. If any are null the <Space> 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?

• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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?
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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)
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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.
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  New release will be next week probably (at least beta version)
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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?
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  Hi,
  the problem was found and fixed. Correct version is in svn and will be included in next release.
  Agata
• 

  oldforum

  • July 16, 2007 08:23

  0

  Comment actions Permalink

  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

Please sign in to leave a comment.