One Star

Lookup issue with expression filter activated

Hi,
I am trying to do a lookup to an oracle table. I have the expression filter activated with the expression :-
row2.date_2.before(row3.END_TIME_DT) && row2.date_2.after(row3.START_TIME_DT)
I have confirmed that both the row2 and oracle db (row3) date are populated but I get a null point exception :-
Exception in component tMap_2
java.lang.NullPointerException
at java.util.Date.getMillisOf(Unknown Source)
at java.util.Date.before(Unknown Source)
at local.jpl_060_validate_schedule_0_1.JPL_060_Validate_Schedule.tFileInputDelimited_2Process(JPL_060_Validate_Schedule.java:8964)
Any ideas?
6 REPLIES
Employee

Re: Lookup issue with expression filter activated

row3.END_TIME_DT and row3.START_TIME_DT are null at expression evaluation.
I think you enabled the lookup filter, so if the current lookup table is row3 you should evaluate your expression in the tMap vars then reused the result in your tMap outputs.
One Star

Re: Lookup issue with expression filter activated

amaumount,
Thanks for your reply but I need to use the lookup filter because I only want to return records from the database that fall within the date range Start/End date. It will not work having to use the variables because I also have two other joins on the lookup table that alone will return multiple rows.
Both the fields on the database and my input file are defined a 'Date' and both have the date format - "MM/dd/yyyy HH:mm:ss".
I don't see why the java is trying to calculate milliseconds because it will be null and I don't need it.
Any other ideas?
I have included a screenshot of my tmap.

Re: Lookup issue with expression filter activated

Have you tried using the output filter in the tMap? do you get the same errors?
Employee

Re: Lookup issue with expression filter activated

Sorry, I was wrong, the normal behavior of the filter is such as the filter is evaluated for each found match, and only for matches, yet I remember that there was a (fixed) problem on this subject because filter was evaluated even if no match was found. Which is your TOS version ?
I think any of results contain null value for row3.END_TIME_DT OR row3.START_TIME_DT, then you should write the following expression:
row3.END_TIME_DT != null && row2.date_2.before(row3.END_TIME_DT) 
&& row3.START_TIME_DT != null && row2.date_2.after(row3.START_TIME_DT)

If you wish allow case where row3.END_TIME_DT can be null you could write:
( row3.END_TIME_DT == null || row3.END_TIME_DT != null && row2.date_2.before(row3.END_TIME_DT) )
&& row3.START_TIME_DT != null && row2.date_2.after(row3.START_TIME_DT)

I hope this help you.
About your question on milliseconds, the methods "before" and "after" needs to compute time in milliseconds before comparing results, and these methods need to have not null parameters.
One Star

Re: Lookup issue with expression filter activated

Hi amaumont,
That seems to have worked- I thought I tried something similiar but maybe my syntax was not quite right.
Thanks again,
Employee

Re: Lookup issue with expression filter activated

To be sure that test with null value is tested you ha to take account on precedence such as described here http://www.uni-bonn.de/~manfear/javaoperators.php
Therefore, when i write
row3.END_TIME_DT == null || row3.END_TIME_DT != null && row2.date_2.before(row3.END_TIME_DT)

if
row3.END_TIME_DT

is null,
row3.END_TIME_DT != null && row2.date_2.before(row3.END_TIME_DT)

is not evaluated