topic Parameters in job for use in SQL query in Talend Studio

Parameters in job for use in SQL query

Anonymous — Mon, 03 Sep 2012 12:11:41 GMT

Hi All.
I m new in Talend ETL (most experience in SSIS).
Talend ver 4.2 and Oracle 11g DB.
How can I do this :
I have view.
I want to load data from this view with parameter
like :
Select * from V1 where f1 = 1
Select * from V1 where f1 = 2
...
Select * from V1 -- ALL data
So I need to pass parameter in job and use it in Query
I do OK it w/o parameters
but waht is way to pass them in job and use it in SQL (in SSIS there vars for that)?

Re: Parameters in job for use in SQL query

Anonymous — Tue, 04 Sep 2012 08:55:38 GMT

Hi All again
I don't find parameters in job
Is there any way (ideas) to resolve this issue ?
May be approach for Talend is wrong and another exists ?

Re: Parameters in job for use in SQL query

Anonymous — Tue, 04 Sep 2012 08:59:31 GMT

Hi and welcome to Talend.
You can set a global var with tSetGlobalVar (for example MyVar) and then pass the value to the query:
Select * from V1 where f1 = "+globalMap.get("MyVar")+"

Re: Parameters in job for use in SQL query

_AnonymousUser — Tue, 04 Sep 2012 09:04:33 GMT

Hi and welcome to Talend.
You can set a global var with tSetGlobalVar (for example MyVar) and then pass the value to the query:
Select * from V1 where f1 = "+globalMap.get("MyVar")+"

Wrong. To set parameters from the outside use context variables, not the global map.

Re: Parameters in job for use in SQL query

Anonymous — Tue, 04 Sep 2012 12:48:35 GMT

Thanks for answers
but how can I make where condition dynamically ?
Select * from V1 -- ALL data
when variable is null ?

Re: Parameters in job for use in SQL query

Anonymous — Wed, 05 Sep 2012 11:04:32 GMT

Hi All
I can set tis var but how can I use it in tOracleInput ?
globalMap.get("v1") in Query ?
?? Can I do smth like
Select * from where f1 = globalMap.get("v1")
or
Select * from where f1 = context.gl_var01

Re: Parameters in job for use in SQL query

janhess — Wed, 05 Sep 2012 11:27:46 GMT

use a context rather than a global variable.
"select * from where f1 = '" + context.contextname + "'"

Re: Parameters in job for use in SQL query

Anonymous — Wed, 05 Sep 2012 12:58:32 GMT

use a context rather than a global variable.
"select * from where f1 = '" + context.contextname + "'"

Hi janhess
Thanks for answer
but
1?) how to convert number to string with context.contextname
2?) - main problem
How can I specify this dynamic sql in query ?
Tried globalMap.get("v1") in Query field - it foes NOt work
( I plan to make dynamic SQL in variable - seems I need TsetGlovalvariable component)

ps
"SELECT * FROM A0.STG_CUSTOMER where stg_customer_id > " + context.v_sys
seems - it works
but I need to pass whole SQL string for case SELECT * FROM A0.STG_CUSTOMER (w/o where condition)
. so I need to run SQL string made dynamically in OracleInput
How can I do that ?

Re: Parameters in job for use in SQL query

Anonymous — Fri, 07 Sep 2012 13:55:46 GMT

Still need to do this
ps Is it my question strange ?
or Talend ETL is not popular enough to get answer to this simple question ?

Re: Parameters in job for use in SQL query

Anonymous — Sat, 08 Sep 2012 03:02:09 GMT

In your DB input component, customize the query. By default, it will be something like "SELECT col1, col2 from table". Change it to something like this: "SELECT col1, col2 from table " + globalMap.get("globalwhereClauseVar") or
"SELECT col1, col2 from table " + context.whereClauseVar.
Note the trailing space you must add after the FROM clause to allow the optional concatenated WHERE clause.
If the WHERE clause variable is an empty string, you select all rows.
The trick is that your variable must contain the entire WHERE clause, i.e. "WHERE col3 in (1,2,3,4)"
Speaking of IN clauses, if you need to set the predicate values dynamically (say you are reading them from a file, or looking them up from another table) a neat trick is to aggregate your predicate values with tAggregateRow using the "list" function. Use a tMap to surround your values list with the rest of the WHERE clause.
My 2 cents on context vars vs. globalMap vars: Context vars for "public" job parameters, globalMap vars for internal "private" global variables.

Re: Parameters in job for use in SQL query

Anonymous — Wed, 19 Sep 2012 14:54:14 GMT

Hi All
I can use variable in dynamic query now
Problem I don't know how to run job with different values of parameter
I can do it manually by swithcin contexts manually
I need now to run job with parameter 4 times
1,2,3,4 in automatic way (not manually) in sequence
:
job1 with par = 1
job1 with par = 2
job1 with par = 3 ....

Seems I need some parent job
May be Prejob is what I needs - but I can't undesrtand how to use it
ps I read docs - sure there is info but can't find it
Any references also will be apreciated

Re: Parameters in job for use in SQL query

alevy — Fri, 21 Sep 2012 07:35:22 GMT

You should start a new topic but what's the source for your four conditions?

Re: Parameters in job for use in SQL query

Anonymous — Fri, 21 Sep 2012 15:31:33 GMT

Hi Alevy - this is continuation of problem
I just need to run job with parameters many times
job1 with par = 1 then , job1 with par = 2 , job1 with par = 56 ....

I can set globalMap.put("par1" , "value1") in job
but don't understand how to pass it it Child job
or how to use context vars for that
I found how I can change them manually (set 2 contexts and change default one in job)
but I need to run Same job in programm way

Or may be I can change context vars in jobs ?

Re: Parameters in job for use in SQL query

janhess — Fri, 21 Sep 2012 15:50:00 GMT

Use tJava to set context value.

Re: Parameters in job for use in SQL query

Anonymous — Fri, 21 Sep 2012 16:17:33 GMT

"Problem I don't know how to run job with different values of parameter"
myjob.bat --context_param whereClause="where col1=6"
myjob.bat --context_param whereClause="where col1=4"
etc...

Re: Parameters in job for use in SQL query

Anonymous — Tue, 25 Sep 2012 10:01:21 GMT

Great Thanks for answers
yes now I need TJava
in future may be I ll need run outside