SQL Placeholder, Context Variable Use, Apply Criteria to Job

[Anonymous]

This is my first job using Talend (Integration Suite 4.1.2) (I spent a year with Pentaho and am switching over..)
Attached images:
-- Business Model
-- Variables I believe I need
-- DB Components
The gist of the job is to pull data from a (NetSuite) DB and insert in a (postgres) DB. I built a job that uses 2 components: tDBInput -> tPostgresqlOutput (DB Component image).
The extract SQL needs to accept a parm (date to be applied to the WHERE clause). Currently, the SQL currently resides in the "Query" section the "Component" tab.

"SELECT
a.ACCOUNT_ID AS account_id,
FROM Administrator.TRANSACTION_LINES a,
WHERE d.ENDING > to_date('12/31/2009','mm/dd/yyyy')"   <<-- needs placeholder (?) for passed parm

I'm still looking for an example on how to pass this parameter.. So I stepped over this and moved on to try and retrieve the date parm value..
The value of the date parm is determined by this logic: (see Business Model image)

If today is a Sunday or the 2nd, 3rd or 4th business day of the month, the job should not run.
If today is Saturday, then set date parm = "12/31/2009 and truncate target table before insert)
If today is any other business day, set the date parm = current_date - 60 and delete last 60 days from target table before insert)

I have a function (postgres) that returns the business day of the month. Using a tPostgresSQLOutput component, I was able to return the current business day of month and see using tLogRow...
I created variables in the Context tab:

vBeginDate - business day function input parm (always current date)
vDayOfWeek - business day function output parm (0-6)
vTruncate - set to true for 0 false for 1-6

I'm just not clear on how to connect all of it together.
1. How do I store the business-day-of-week function return value (date) to pass to tDBInput?
2. Once I have this value, what component might I use to exercise the logic? (tpostgresqlInput -> t??? -> tDBInput -> tpostgresqlOutput ??)
3. Where can I find an example of how to build the SQL in tDBInput to have a placeholder and how to pass it in?
I know this is long winded, but as I mentioned, first day..
Any suggestions are appreciated..
Mark

[Anonymous]

To pass the parameter to the tDBInput component..and account for the business logic..
components: tJava --iterate--> tDBInput
variables: vBeginDate (date), vStringBeginDate (string), vTruncate, vDayOfWeek
In tJava, enter some variation of the following:

context.vDayOfWeek = row2.biz_days;    /* comes from the biz day function you ref'd */
context.vFullPullDate = TalendDate.parseDate("MM/dd/yyyy","01/01/2010");
context.vTruncate = 0;
context.vBeginDate = TalendDate.getCurrentDate();
if (context.vDayOfWeek >=1 || context.vDayOfWeek <= 5) {
	context.vTruncate = 0;
	context.vStringBeginDate = TalendDate.formatDate("MM/dd/yyyy", context.vBeginDate);
}; 

/* else if (context.vDayOfWeek == 0) {
	context.vTruncate = 1;
	context.vBeginDate = TalendDate.parseDate("MM/dd/yyyy",context.vFullPullDate));
}; */

In the SQL query in tDBInput
change this:

"SELECT
a.ACCOUNT_ID AS account_id,
FROM Administrator.TRANSACTION_LINES a,
WHERE d.ENDING > to_date('12/31/2009','mm/dd/yyyy')"   <<-- needs placeholder (?) for passed parm
to
"SELECT
a.ACCOUNT_ID AS account_id,
FROM Administrator.TRANSACTION_LINES a,
WHERE d.ENDING > to_date( '"+context.vStringBeginDate+"', 'mm/dd/yyyy')"

In a conference call today with Talend and Jasper support reps we found out that the perl side of this may not be supported going forward. That's too bad for us.. (as you can see by my Java ) I'm better off with perl.. I don't know if we'll stay with this tool or not .. still fun for a day or so!