topic Re: how to pass a variable in a tMSSQLInput based on a query ? in Talend Studio

how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Sat, 16 Nov 2024 14:33:22 GMT

hello
I would like to use one SQL query that could vary based upon one variable, how can I pass this parameter in the SQL query ?
David

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Thu, 06 Sep 2007 13:50:44 GMT

Perl or Java project?

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Thu, 06 Sep 2007 13:53:23 GMT

Java :
"SELECT * from TABLE
where S.CODE_PER IN ('2007070','2007080','2007090') "
I need to pass ('2007070','2007080','2007090') as a variable : current month, month -1 and month -2 as YYYYMM0

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Fri, 07 Sep 2007 12:20:31 GMT

Hi dcopp.
Try this (I've discover it today):
"SELECT * from TABLE
where S.CODE_PER IN ('"+context.getProperty("date")+"',ecc) "
Bye

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Fri, 07 Sep 2007 15:43:48 GMT

hello
How did you define your context ?
I don't get any rows with such script, more the 'ecc' column is unknown to me
Anybody succeeded in passing a variable value into a SQL script ?

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Fri, 07 Sep 2007 19:38:05 GMT

Under tab "Repository" there is an icon called "Contexts". Right click->create context
Bye

Re: how to pass a variable in a tMSSQLInput based on a query ?

c0utta — Mon, 10 Sep 2007 02:37:13 GMT

dcopp,
All my queries use context variables with MSSQL - have a look at my post 1164. The screen print will show you how to use context variables with in a query. As long as the query resolves to a string, that's all that matters.
Can I also be so bold as to suggest to read the User Guide? Contexts are clearly documented in there, as are creation of context variables.
Cheers,
c0utta

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 19 Aug 2008 01:39:48 GMT

Ok, I have tried to find the answer in many places in this forum but I haven't been successful.
I have two table: one with the unique keys and a second with the associated values.
So I use a first tMysqlInput to get the keys (select id from id_table) --> Iterate --> tMysqlInput (select data from data_table where data_id = tMysqlInput.id)
My issue is that I don't know how to iterate on the id result from the first query and use the query result as a parameter in the second component.

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 19 Aug 2008 06:02:12 GMT

Hello raton

So I use a first tMysqlInput to get the keys (select id from id_table) --> Iterate --> tMysqlInput (select data from data_table where data_id = tMysqlInput.id)

For example:
tMysqlInput--row1-->tLogRow
|
iterate
|
tMySQLInput("select data from data_table where data_id ="+String.valueOf(row1.id))
Best regards

shong

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Wed, 20 Aug 2008 01:54:50 GMT

I can feel that I am getting a lot closer to the solution.
The equivalent of that line "tMySQLInput("select data from data_table where data_id ="+String.valueOf(row1.id)) " for Perl would be ... ??
(sorry for the extra question, I just saw this topic was tagged "java" ... )

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Wed, 20 Aug 2008 22:32:48 GMT

I have had many unsuccessful attempts:
"select data from data_table where data_id = " + $row1
"select data from data_table where data_id = " . $row1
"select data from data_table where data_id = " + $row1.id
"select data from data_table where data_id = " + $row1.id
Then I had the epiphany:
"select data from data_table where data_id = " . $$row1
I thought I would post this as I hope I am not the only person not using Java.
Thanks shong for putting me on the right track

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Tue, 07 Oct 2008 04:13:58 GMT

hi, i can't move in some urgent project.can somebody help me?
i want to pass the parameter in a subquery. how to do it unsing context?

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 07 Oct 2008 06:06:10 GMT

Isn't your question answered by this thread?
What else do you want to know or what is you actual problem?

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Tue, 07 Oct 2008 06:38:03 GMT

my problem is inside the sub query i have to pass the context variable and it is not detecting.

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 07 Oct 2008 06:39:00 GMT

Can you post the query you are trying to use?

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Tue, 07 Oct 2008 06:53:51 GMT

select distinct uthip.current_status_history.tester,uthip.current_status_history.device,
uthip.current_status_history.lotnumber,uthip.current_status_history.tester_status,
((select distinct uthip.current_status_history.total_units from uthip.current_status_history where
uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"'
and uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')
and uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and
(now()) and uthip.current_status_history.datetime =(select max(uthip.current_status_history.datetime)
from uthip.current_status_history where uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"'
and uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')and
uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and (now()) ))
-
(select distinct uthip.current_status_history.total_units from uthip.current_status_history
where uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"'
and uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')and
uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and
(now())and uthip.current_status_history.datetime =(select
min(uthip.current_status_history.datetime) from uthip.current_status_history where
uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"'
and uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')
and uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and (now()))))
as total_units
from uthip.current_status_history where uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"' and
uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')
and uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and (now())

this is the query
1: "select distinct uthip.current_status_history.tester,uthip.current_status_history.device,
uthip.current_status_history.lotnumber,uthip.current_status_history.tester_status,"
from this section,it is giving correct value.
2:from uthip.current_status_history where uthip.current_status_history.tester='\"+(String)globalMap.get(context.tester)+\"' and
uthip.current_status_history.datetime_start < (timestamptz(date(text(now())) - 1)+'08:00:00')
and uthip.current_status_history.datetime between (timestamptz(date(text(now())) - 1)+'08:00:00')and (now())
from this section also giving correct value.
3: but from the middle section it can't detect the variable. but the query is correct and i have tested it.

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 07 Oct 2008 07:00:52 GMT

I think you do not need the global.map.
Please change your code to:

//Old code
(String)globalMap.get(context.tester)
//New code
(String) context.tester
// Additional I think you do not need the (String) conversion. But I'm not sure in this point.

Bye
Volker

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Tue, 07 Oct 2008 07:07:11 GMT

thanks a lot,it is working

Re: how to pass a variable in a tMSSQLInput based on a query ?

Anonymous — Tue, 07 Oct 2008 07:17:44 GMT

You are welcome.
By the way (and for everyone which will have the same problem in the future): globalMap is a hash variable which stores different information of the components (like the fileName of a tFileList for example). If you use a tFlowToIterate the row itself will be stored in the globalMap. The context is a dedicated variable which is not stored in the hash so you can just use it.
Bye
Volker

Re: how to pass a variable in a tMSSQLInput based on a query ?

_AnonymousUser — Wed, 08 Oct 2008 07:07:58 GMT

Hi, can i pass a object to the sub job? if can, then to do it?