Parameters in job for use in SQL query

[Anonymous]

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

[Anonymous]

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 ?

[Anonymous]

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")+"

[_AnonymousUser]

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.

[Anonymous]

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

[Anonymous]

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

[janhess]

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

[Anonymous]

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 ?

[Anonymous]

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 ?

[Anonymous]

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.