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

[Anonymous]

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

[Anonymous]

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

[_AnonymousUser]

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?

[Anonymous]

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

[_AnonymousUser]

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

[Anonymous]

Can you post the query you are trying to use?

[_AnonymousUser]

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.

[Anonymous]

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

[_AnonymousUser]

thanks a lot,it is working

[Anonymous]

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

[_AnonymousUser]

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