How to filter input on value from lookup flow?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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:

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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?