Trouble with Lookup

CloverDX Customer Portal
Forum
Trouble with Lookup

shanepresley

January 21, 2008 00:00

Answered

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

Comments 7

jurban

January 21, 2008 17:10
0

Comment actions Permalink
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
shanepresley

January 21, 2008 17:58
0

Comment actions Permalink
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(<id>,<key_part1>[,key_part2, …]).<field_name>

<id> - 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
dpavlis

January 21, 2008 20:18
0

Comment actions Permalink
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.
shanepresley

January 21, 2008 23:12
0

Comment actions Permalink
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"?>
<Graph created="Sun Jan 20 00:46:36 CST 2008" guiVersion="1.8" licenseType="Evaluation license." modified="Sun Jan 20 17:38:51 CST 2008" name="SimpleLookupFunction" revision="1.16">
<Global>
<Metadata id="Metadata1">
<Record name="input" recordSize="-1" type="delimited">
<Field delimiter="|" name="Field1" nullable="false" shift="0" type="numeric"/>
</Record>
</Metadata>
<Metadata id="Metadata0">
<Record name="name_lookup" recordDelimiter="\n" recordSize="-1" type="delimited">
<Field delimiter="|" name="id" nullable="false" shift="0" type="string"/>
<Field delimiter="|" name="name" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata2">
<Record name="output" recordSize="-1" type="delimited">
<Field delimiter="|" name="Field1" nullable="false" shift="0" type="string"/>
<Field delimiter="|" name="name" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<LookupTable dataType="delimited" fileURL="/dat/lookups/surnames.dat" id="LookupTable0" key="id" metadata="Metadata0" name="surname" type="simpleLookup"/>
</Global>
<Phase number="0">
<Node enabled="enabled" fileURL="/dat/lookups/surnames.dat" guiHeight="0" guiName="Delimited Data Reader" guiWidth="0" guiX="91" guiY="16" id="DELIMITED_DATA_READER0" type="DELIMITED_DATA_READER"/>
<Node enabled="enabled" freeLookupTable="false" guiHeight="0" guiName="lkp_surname" guiWidth="0" guiX="597" guiY="33" id="LOOKUP_TABLE_READER_WRITER0" lookupTable="LookupTable0" passThroughInputPort="0" passThroughOutputPort="0" type="LOOKUP_TABLE_READER_WRITER"/>
<Node enabled="enabled" guiHeight="0" guiName="Sort" guiWidth="0" guiX="332" guiY="10" id="SORT0" sortKey="id" type="SORT"/>
<Edge fromNode="DELIMITED_DATA_READER0:0" guiBendpoints="" id="Edge18" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="SORT0:0"/>
<Edge debugMode="true" fromNode="SORT0:0" guiBendpoints="" id="Edge2" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="LOOKUP_TABLE_READER_WRITER0:0"/>
</Phase>
<Phase number="1">
<Node enabled="enabled" guiHeight="0" guiName="Data Generator" guiWidth="0" guiX="64" guiY="161" id="DATA_GENERATOR0" randomFields="Field1=random("1","50")" recordsNumber="10" type="DATA_GENERATOR"/>
<Node enabled="enabled" guiHeight="0" guiName="Reformat" guiWidth="0" guiX="274" guiY="162" id="REFORMAT0" passThroughInputPort="0" passThroughOutputPort="0" type="REFORMAT">
<attr name="transform"><![CDATA[function transform(){
$Field1:=num2str($Field1);
$name:=lookup(LookupTable0,num2str($Field1)).name;
}]]></attr>
</Node>
<Node enabled="enabled" guiHeight="0" guiName="Trash" guiWidth="0" guiX="566" guiY="182" id="TRASH0" type="TRASH"/>
<Edge fromNode="DATA_GENERATOR0:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (out)" toNode="REFORMAT0:0"/>
<Edge debugMode="true" fromNode="REFORMAT0:0" guiBendpoints="" id="Edge1" inPort="Port 0 (in)" metadata="Metadata2" outPort="Port 0 (out)" toNode="TRASH0:0"/>
</Phase>
</Graph>
shanepresley

January 21, 2008 23:15
0

Comment actions Permalink
- 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]
avackova

January 24, 2008 13:58
0

Comment actions Permalink
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.
shanepresley

January 25, 2008 16:19
0

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

Thanks,
Shane

Please sign in to leave a comment.

Quick links

Access my products

SUPPORT & SERVICES

Community

RESOURCES