Talend community forum / How to pass filtering parameters into WHERE clause of SELECT stmt?

kah2020 · 2007-08-15 08:12:54

Hi,

One questions regarding the tDBInput or similar component.

I want to syn database from a transaction database (TXDB) into my BI database (BIDB) periodically.

For the very first syn, I will pull all rows of relevant tables from TXDB to BIDB.

However, subsequently for everyday, I will want to say syn just rows belonging to 1 month before today and 1 month after today. How can I achieve this? i.e. is there anyway to pass in filtering parameters into the WHERE clause of the QUERY property of tDBInput?

Thanks
TOS Recruit

mhirt · 2007-08-15 16:47:57

Hello,

Adding parameters to your SQL query is very easy.
Here is an example in Java :

Code:

"select id, name from employee where field_date>'"+ minDate + "' and field_date<'" + maxDate + "'"

The question is how to initialize your parameters.

In Java, my advice is to add a new routine :

Code:

//template routine Java
package routines;

import java.util.Calendar;
import java.text.SimpleDateFormat;
import java.text.FieldPosition;
import java.util.Date;

public class MyRoutine {
    public static String dynamicDateInitExample(int diff) {
        StringBuffer result = new StringBuffer();
        
        Calendar myCalendar = Calendar.getInstance();
        myCalendar.add(Calendar.MONTH, diff);
        Date myMinDate = myCalendar.getTime();
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        sdf.format(myMinDate, result, new FieldPosition(0));
        return result.toString();
    }
}

and then in your DBInput Query :

Code:

"select id, name from employee where field_date>'" + MyRoutine.dynamicDateInitExample(-1) + "' and field_date<'" + MyRoutine.dynamicDateInitExample(1) + "'"

Regards,
Michaël

Last edited by mhirt (2007-08-15 16:49:12)

kah2020 · 2007-08-16 03:03:19

Hi, Michael

Thank you very much. That really help.

Best Regards
Kah

Announcement

Unanswered posts

#1 2007-08-15 08:12:54

How to pass filtering parameters into WHERE clause of SELECT stmt?

#2 2007-08-15 16:47:57

Re: How to pass filtering parameters into WHERE clause of SELECT stmt?

Code:

Code:

Code:

#3 2007-08-16 03:03:19

Re: How to pass filtering parameters into WHERE clause of SELECT stmt?

Board footer