topic Re: Filtering on boolean value (tMap / tFilterRow) - creating WHERE clause in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 13:07:44 GMT

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

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

Anonymous — Mon, 24 Jan 2011 21:39:06 GMT

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

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

Anonymous — Tue, 25 Jan 2011 09:17:52 GMT

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?

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

Anonymous — Tue, 25 Jan 2011 10:19:47 GMT

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

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

vladika — Tue, 25 Jan 2011 12:12:32 GMT

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

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

Anonymous — Tue, 25 Jan 2011 13:34:00 GMT

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?

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

vladika — Tue, 25 Jan 2011 14:01:53 GMT

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

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

Anonymous — Tue, 25 Jan 2011 15:53:40 GMT

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.

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

Anonymous — Mon, 31 Jan 2011 10:22:09 GMT

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