One Star

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

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?).
8 REPLIES

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

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

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

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?
One Star

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

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
Four Stars

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

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..
One Star

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

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?
Four Stars

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

"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.
One Star

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

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.
One Star

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

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... Smiley Happy