topic Re: How to filter input on value from lookup flow? in Talend Studio

How to filter input on value from lookup flow?

Anonymous — Sat, 16 Nov 2024 13:52:36 GMT

Hi
I want to read a delimited file and insert the rows into a database table where file.column0 > max(database.table.primary_key).
I've created my delimited file main input and my database output OK. I've also created a query as a lookup flow:
select max(recdate) from vws
However I can't figure pout how to connect these with the right components to extract only the data I want. I did think that tFilterRow would do the job, particularly as the reference Guide says:
Compares a column from the main flow with a reference column from the lookup flow
However all the examples (and my trials with it) suggest that it can't read a lookup flow, you have to hard code the values you want to filter on. I want to get the value to filter on from thee database each time the job is run.
Hopefully someone can point me in the right direction.
Thanks
David

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 03:34:55 GMT

Hello David
First, define a context var called maxDate.
txxxInput(select max(recdate) from vws) -->tJavaRow(on tJavaRow, type in: context.maxDate=input_row.max)
|
onSubjobOk
|
tFileInputDelimited-->tFilterRow(add one condition, input column:column0, Function: empty, operator ,value:context.maxDate)-->txxxOutput
Best regards
shong

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 10:05:57 GMT

Hi Shong
Many thanks for your help. I tried to follow your instructions; but I guess I've missed something becuase I'm getting a Java error when I run the Job:
Execution failed :Failed to generate code.

I'll upload an image of the overall map below.
The database query (called max_recdate in the job) executes the SQL:

select max(recdate) AS max_recdate from vws

This feeds into my tJavaRow component that executes:

context.maxDate=input_row.max_recdate;

I've been through the schemas and checked that the columns referenced in the comparison are all Long (value is stored as YYYYMMDDHHMISS)
So what have I done wrong?
Thanks
David

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 10:27:31 GMT

Hello
Please upload a screenshot of basic setting of tFilterRow.
Best regards
shong

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 10:47:23 GMT

Hi
Here's my tFilterRow. In my schema the I've renamed the first column "recdate".
Cheers
David
Drat, image too big, will try again.

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 11:01:19 GMT

I think I've gotten a bit further, I don't think I'd sync the schemas in one of the components because I'm not getting the Java exception anymore; but now I'm getting these errors:

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 11:21:33 GMT

Still making progress. I managed to get rid of the above errors. One was because in my tJavaRow component I'd called the context variable MaxDate instead of maxDate. The other two rows were beuase I'd ticked Advanced in the tFilterRow which had inserted extra code into the filter.
Now I'm getting another Java exception on executing the job. The error I get is:

Exception in component tFilterRow_1
java.lang.NullPointerException
	at vws_update_j.vws_update_0_1.VWS_Update.tFileInputDelimited_2Process(VWS_Update.java:6323)
	at vws_update_j.vws_update_0_1.VWS_Update.runJobInTOS(VWS_Update.java:8494)
	at vws_update_j.vws_update_0_1.VWS_Update.main(VWS_Update.java:8389)

Line 6323 is the filter comparison:

if ((row1.recdate > context.maxDate))
{

So I'm guessing (because I'm not a Java expert) that either row1.recdate or context.maxDate don't point to anything. You can see my next question coming; why not?
Regards
David

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 12:44:35 GMT

Hello
first, you should make sure you have defined a context var called maxDate in context tab.
second, you add a tLogRow component between tmysqlInpust and tJavaRow to display max_recdate on console, if it is null or not.
Best regards
shong

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 16:05:25 GMT

I believe I have created my context variable - screen shot included.
I inserted the tLogRow and the max_recdate has a value - so it seems it's a problem with the context variable?
Regards
David

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 16:37:16 GMT

Here's another thing I don;t understand that my be related to why this isnt working.
To check the output of the tFileDelimited component I inserted a tLogRow between my tFileDelimited and tFilterRow. The tLogRow shows correct data values on the left hand side; but null values on the right hand side. So what's happening to the values?

Re: How to filter input on value from lookup flow?

Anonymous — Mon, 13 Jul 2009 17:32:41 GMT

Hi
I did some more rooting around in the forum and tests with my job. I put a tJavaRow between my tFileDelimited and my tFilterRow and included a system call to pint the value of context.maxDate. Sure enough it was null.
Then I found this thread https://community.talend.com/t5/Design-and-Development/tRunJob-retrieve-context-variable-value-redefined-inside-a/td-p/62372 which seems to suggest that you cannot pass context vairable values from a child job to its parent without writing the value to a temp store and reading it back in again.
That would explain why the context variable has a correct value in the top part of the job and a null value in the main flow. Is there a simple way around this?
Regards
David

Re: How to filter input on value from lookup flow?

Anonymous — Tue, 14 Jul 2009 03:00:06 GMT

Hello David
Your job is different from topic1083. There are a father job and some child jobs in topic1083, but your job is a single job. I think you have miss someting or have some bad settings. Please export your job and send it to me via email, I will check it.
Best regards
shong