N00b Question on ExtHashJoin

CloverDX Platform
1

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

2

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.

3

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!!!

4

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.