One Star

Dynamically filtering column with Null values

I am looking for inputs on how to dynamically filter columns that has null values and map that to tMSSqlOutput.
Here is the scenario i am running into.
We have to set default values for may fields that are inserted into sql server db, problem is inserting records using tMSSqlOutput don't default column since null is passed and if column values are passed(with null) they don't get defaulted in database.
So question is how can i filter out null columns before i connect that to tMSSqlOutput? so basically right between tMap and tMSSqlOutput i want to filter out columns that have null so they don't appear in insert statement that is generated by tMSSqlOutput component.
I am using TIS 4.2.3
Job looks like
tFileInputExcel
|
|
tMap
|
|
tMSSqlOutput
4 REPLIES
Community Manager

Re: Dynamically filtering column with Null values

Hi
You can add a filter expression on the output table on tMap, for example:
row1.columnName!=null
Let me know if you have any questions!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Dynamically filtering column with Null values

Thanks shong. I tried your suggested approach but i don't think it will work for our scenario. Based on your suggestion if we add expression then it will ignore complete Row if name is null. What we want is we need to ignore just the column "name" if its null and rest of the data should be inserted as it is.
As shown in attached snapshots #3, basically we want "tMSSqlOutput_1" to ignore column that is null so insert statements are created as shown in snapshot #3
We have so many columns that needs to be defaulted so we want to use default constraint define in DB to default values instead of setting them in talend. Just like "tFilterRow" filters out row can we filter out columns that are null, i tried "tfilterColumns" but don't know how to filter null columns using that.
Please let us know how this can be handled in Talend. Appreciate your help in solving this problem
Community Manager

Re: Dynamically filtering column with Null values

Hi
I understand you now, unfortunately, it is impossible to filter columns at run time, as a workaround, you can add an expression to check and set a default value to it if the value is null, for example:
in the expression filed of department column of output table on tMap:
row1.department==null?"the default value":row1.department
From your images, I see the default value is empty, so the expression could be:
row1.department==null?"":row1.department
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Dynamically filtering column with Null values

Hi All,
I started exploring Talend to generate xls file based on dynamic query. I will pass query to Job externally and I will be look like select col1, col2 from mytable or select col3, col4, col2 from mytable. I don't have fixed column in select.
I am using tQracleInput and tMSOutputExcel for output. Input node schema is depend on select block. Please help me to achieve my requirement if possible in talend.
Thanks,
so