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

One Star

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

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
Employee

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

Perl or Java project?
One Star

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

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
One Star

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

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

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

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 ?
One Star

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

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

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

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
One Star

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

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.
Community Manager

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

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

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

I can feel that I am getting a lot closer to the solution. Smiley Happy
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" ... )
One Star

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

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 Smiley Happy
One Star

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

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?
One Star

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

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

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

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

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

Can you post the query you are trying to use?
One Star

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

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.
One Star

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

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
One Star

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

thanks a lot,it is working
One Star

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

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
One Star

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

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

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

I think you could pass a (java) object too. But I haven't tried it.
If the variable is internal converted to a generic type like string, this should not work.
I think, give it a try and report the result ;-)
Bye
Volker
One Star

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

hi,
i have a table with columns like good, total, yield
i have to pick good and total column values and
insert yield= good*100/total
it is giving error. may be due to the same table
how can i do it?
One Star

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

Can you give an example of your job? Where is your data stored, what error do you get?
If you use a database could your use UPDATE table SET yield = good*100/total WHERE xxx ?
Bye
Volker
One Star

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

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

Trying shong's solution, I find that, when there are quite few outputs from the 1st tMysqlInput (i.e. row1, and hopefully "iterate"), nothing's outputed from the 2nd tMysqlInput (row2). No data at all, when there should be some !
It's only with about 250 lines that I get data in row2.
Did I miss something ?
One Star

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

I used a query like this and it worked fine using a context variable named context.FileName.
"select some_column from mytable where some_column = '"+context.FileName+"' "
It is similar to how you would create dynamic SQL if you were doing it in MSSMS.
One Star

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

Hi Everyone,
For mssql input, I gave below query. But it is throwing an Exception "Conversion failed when converting date and/or time from character string".
select * from test where CREATE_DATE>CONVERT(datetime,'("+context.last_run_date+")') or modified_date>CONVERT(datetime,'("+context.last_run_date+")')
context.last_run_date is passing date as string(1950-01-01 00:00:00) which I am trying to convert as date datatype using CONVERT. 
Please suggest me the solution how to convert it.