N00b Question on ExtHashJoin

pwmcmaho

• June 27, 2011 00:00

• Answered

Follow

Hi all, just started playing with CloverETL and have a question on building transformations for a full outer join.

How do you code the CTL2 for the transform, for the situations where you want common fields to be merged in the resulting output? (sorry, might not be the best wording).

For example, if I have two inputs that have a key field (call it key_1) and I am doing a full outer join, I would expect to see joined records for those records where the key_1's match... and since I am doing a full outer join, I would expect to see all records passed through the output.

In my transformation, where I would normally have something like $0.key_1 = $0.key_1, I would really like it to be based on either the master or slave, depending on which one is not null, so that it will always have a value.

I've tried variations of if ... else, as well as ternary iif ... but am not able to get it to work... I either get one set, one time, or neither set... but not the combination of the two...

I can do a simple "+", but when the value of one or the other is null, I end up with a VaLuEnull ("null" in the result value).

Thanks!!!!

Patrick

• 

  martin

  • June 28, 2011 11:24

  0

  Comment actions Permalink

  Hello Patrick,
  
  in order to see all records passed through the output based on either master or slave, in transformation you should use nvl(arg1,value) function which returns value if arg1 is NULL otherwise return arg1. Please look at example:
  
  function integer transform() {
  $0.key_1 = nvl($0.key_1,$1.key_1);
  
  return ALL;
  }
  
  Best regards Martin.
• 

  pwmcmaho

  • July 18, 2011 19:14

  0

  Comment actions Permalink

  Martin - excellent - thanks!
  
  How can you do that with more than one, for example, if I have three fields I am potentially merging:
  
  $0.fielda = $0.fielda + $1.fielda + $2.fielda
  
  How do I ensure that all null values are removed when concatenating?
  
  Thanks!!!!
• 

  avackova

  • July 21, 2011 09:11

  0

  Comment actions Permalink

  Hello Patrick,
  do it in the same way as in Martin's answer:
  

  $0.fielda = nvl($0.fielda,"") + nvl($1.fielda,"") + nvl($2.fielda,"");
  

  for concatenating or:

  
  $0.fielda = nvl($0.fielda,nvl($1.fielda,$2.fielda));
  

  for selecting only one (not-null) key.

Please sign in to leave a comment.