Trouble with Lookup

CloverDX Platform
1

All,
I am having a bit of trouble with the lookup function. I am using CloverGUI and am trying to use the Clover ‘language’ in a simple reformat. Here is my very, very, very simple code:

function transform(){
$cust_id := $cust_id;
$lst_nm := lookup(LookupTable0,‘surname_id’).name;
}

The error message I am getting is:

ERROR [WatchDog] - Node REFORMAT0 finished with status: ERROR caused by: Interpreter runtime exception on line 2 column 21 : when mapping “$lst_nm” (string) - assigning “null” (unknown type)

I have gone so far as to hard code the value of the field ‘surname_id’ in the input to ensure that I get a match.

Can someone please tell me what I am doing wrong here? What I was to do is so incredibly straightforward I can’t believe I am having this much trouble doing it.

Thanks!
Shane

2

Hi,
the line with the lookup function tries to find an entry in the lookup table under the key “surname_id”, it doesn’t read the key from the input. you should probably write something like:
$lst_nm := lookup(LookupTable0,$surname_id).name;

Jaro

3

Hi,
the line with the lookup function tries to find an entry in the lookup table under the key “surname_id”, it doesn’t read the key from the input. you should probably write something like:
$lst_nm := lookup(LookupTable0,$surname_id).name;

Jaro

“jurban”

Jaro,
Thanks for the prompt reply, unfortunately that is not working either (same error). To make sure I am understanding this correctly:

lookup(,<key_part1>[,key_part2, …]).<field_name>

- The generate unique name given to the lookup file (NOT the name you specify in the wizard)

<key_part[N]> - This is not the input field(s) corresponding to the lookup key? I thought using $input_field should use the value in input_field and match it to the lookup key, correct? The example shows this enclosed value enclosed in single quotes (which to me indicates a string constant) so I tried that.

<field_name> - This is the name of the field on the lookup file that you wish to assign to the output, correct?

I don’t see an example of the lookup *function* in the examples. Am I missing it? The error seems to imply that Clover is not aware of the datatype of <field_name> and therefore it cannot complete the mapping. Could that be it? Am I supposed to be initializing something? I have a ‘Delimited Data Reader’ connected to a Lookup Table Reader/Writer… I am not sure what else I could do.

Thanks for any help!
Shane

4

HI !

The error message is simply (well, not so clearly ) saying that you try to assign null value into output field which can’t be set to null - at least that seems from the msg.

The lookup(lkid,key par1,part2…) function takes the lookup table indicated by “lkid” and using key part1,part2,etc tries to look appropriate record and take value of “name” field from it.

It does not matter wheter part1 is hardcoded string or reference to variable or input field. Important things:

- is the LookupTabple0 defined the way that it has only 1 field part of its key ?
- is the key a string type of filed (that is what your code suggests)
- do the records stored in the table have “name” field ?
- is , in the table really a record with key field containing ‘surname_id’ value ? (I guess not, it is rather name of the field, not content)

David.

5

The error message is simply (well, not so clearly ) saying that you try to assign null value into output field which can’t be set to null - at least that seems from the msg.

“dpavlis”

That’s what I thought too, but the target field is nullable.

The lookup(lkid,key par1,part2…) function takes the lookup table indicated by “lkid” and using key part1,part2,etc tries to look appropriate record and take value of “name” field from it.

It does not matter wheter part1 is hardcoded string or reference to variable or input field.

“dpavlis”

I am very glad to hear that!

- is the LookupTabple0 defined the way that it has only 1 field part of its key ?

“dpavlis”

Yes

- is the key a string type of filed (that is what your code suggests)

“dpavlis”

Initially it was an int. I have tried it with a string but with the same results. When you say ‘filed’, do you mean that it needs to be padded?

- do the records stored in the table have “name” field ?

“dpavlis”

Yes

- is , in the table really a record with key field containing ‘surname_id’ value ? (I guess not, it is rather name of the field, not content)

“dpavlis”

Yes. ‘surname_id’ is a valid field name in the input.

I have included my code and the expanded error message below. It is a very simple graph, I have to think that I am just missing something.

Thanks again!
Shane

Error Message:
DEBUG [WatchDog] - Node REFORMAT0 error details:
org.jetel.interpreter.TransformLangExecutorRuntimeException: Interpreter runtime exception on line 2 column 18 : when mapping “$name” (string) - assigning “null” (unknown type)

at org.jetel.interpreter.TransformLangExecutor.visit(TransformLangExecutor.java:1367)
at org.jetel.interpreter.ASTnode.CLVFMapping.jjtAccept(CLVFMapping.java:32)
at org.jetel.interpreter.TransformLangExecutor.executeFunction(TransformLangExecutor.java:1465)
at org.jetel.component.WrapperTL.executePreparedFunction(WrapperTL.java:326)
at org.jetel.component.RecordTransformTL.transform(RecordTransformTL.java:92)
at org.jetel.component.Reformat.execute(Reformat.java:196)
at org.jetel.graph.Node.run(Node.java:366)
at java.lang.Thread.run(Unknown Source)

#####################################################################################
## Code
#####################################################################################

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
6

- is , in the table really a record with key field containing ‘surname_id’ value ? (I guess not, it is rather name of the field, not content)

“dpavlis”

I have tried a hardcoded value that is in the lookup file and I get the same error message :? [/i]

7

Hi,
it seems you have found a bug in Transform Language. You have to write transformation in java language or use LookupJoin component in the mean time.

8

OK, thanks. I also filed a bug in Mantis:
http://bug.cloveretl.org/bug_view_page.php?bug_id=247

Thanks,
Shane