Filtering on boolean value (tMap / tFilterRow) - creating WHERE clause

[Anonymous]

Hi,
I can not seem to filter on a boolean value, every type of syntax I use renders error messages.
What is the right syntax and where is the best place to write the filter expression?
What I want to achieve is to only process the input rows that have the "deleted" field set to "1" (which I guess could also mean true?).

[Anonymous]

if the datatype is Boolean:
row1.deleted == true
if the datatype is Integer:
row1.deleted == 1

[Anonymous]

if the datatype is Boolean:
row1.deleted == true
if the datatype is Integer:
row1.deleted == 1

Thanks, that is what I tried - the datatype is boolean. I actually tried all the options 1, true, "1" and "true" (with quotes).
Then the next question to find the source of the errors is to understand where to write this. There seems to be many places to enter a filter expression. Basically I just want to have the same effect as a "where" clause, really!
I have tried expression field at the end of the Main input flow, I have tried on the Output flow, I have tried adding a tFilterRow but to no avail. What basic Talend concept am I missing when it comes to emulating a "where" clause in the flow?

[Anonymous]

Actually, to help a bit more, the error I get is this:

Exception in component tFilterRow_1
java.lang.NullPointerException
	at import_into_sugarcrm.sugartoexcel_0_1.SugarToExcel.tMysqlInput_1Process(SugarToExcel.java:4148)
	at import_into_sugarcrm.sugartoexcel_0_1.SugarToExcel.runJobInTOS(SugarToExcel.java:6116)
	at import_into_sugarcrm.sugartoexcel_0_1.SugarToExcel.main(SugarToExcel.java:5987)

tFilterRow_1 just has one line in it:
InputColumn: deleted --- Function: Empty --- Operator: Equals --- Value: false

[vladika]

My 2 cents.... Maybe the column you're filtering is defined as not nullable (has the option "nullable" unchecked), and you're filter is encountering a null entry? Check the nullable option and add this possibility. For example use Advanced mode and type:

input_row . deleted == null || input_row . deleted == true
I believe this should output Nulls and TRUEs..

[Anonymous]

Thank you.
Yes I already started thinking it would have to be NULL values playing tricks and this combination of null or true/false avoids the NullPointerException, however I still do not get any of the null values.
I am using the Expression Filter field of row1 (input) and can't seem to find any nullable settings for it?

[vladika]

"Nullable" is a field setting, not the component setting, so you will see it in "Edit schema" window.
Maybe this will work:
ISNULL(input_row . deleted) || input_row . deleted == true
, but basically it is the same like previous one. You can see the definition of the ISNULL method if you go to: Repository/Code/Routines/System/Relational.

[Anonymous]

Ah, you mean if the SQL field is allowed to be NULL or not - yes the field "deleted" is marked as nullable in the schema, this was guessed automatically (and well done too) by the Talend schema wizard.
Still no luck, I simply cannot get the NULL values out into my Excel file, it is very peculiar.

[Anonymous]

I had a slight revelation on this topic that I should probably share; it turned out that there was an exact match between another part of the logic and a lack of NULL values, so everytime I filtered on X it also showed no NULL values. The wonders of remembering to change only one variable at the time when searching for the cause of a problem...