Dynamic query

[Anonymous]

Our DW is in MySQL, with inputs from both MySQL and MSSQL transactional systems and Access (for legacy data).
Every night we wish to load in the latest transactions. (Each transaction is uniquely numbered in the source).
We keep a record of the last transaction in a table in the DW.
How do we use the result of a tMysqlRow component to modify the query on a MSSQLInput or MySQLInput job? For example:
tMySQLRow select lastransactionid from transactionhistory where transactiontype='web';
tMSSQLRow/tMySQLRow select .... from transactiontable where transactionid > x ... (where x is the output from tMySQL Row)
We have looked at using tMap, but the performance appears to be slow as it traverses the remote systems row by row. If we can modify the query we can reduce the amount of data transferred.
Thanks
ce

[Anonymous]

Did some additional research (manual is not too explicit in these areas)...
1. Created contextvariable
2. Created MysqlInput job to query for single row, single field field
3. Created tJavaRow job (from (2)) to context.setProperty("contextvariable",row1.singlefield)
4. Then Run, MSSqlInput job with an amended query: "SELECT ... FROM ...."+(String)((String)context.getProperty("contextvariable"))
Works perfectly.

[Anonymous]

Good solution contentengineer. A more TOS way to do things would be to replace the tJavaRow with a tContextLoad (so that you don't use manual code). Anyway, using a context variable is a good solution. In my opinion, it would be better to use a simple "global" variable because context is a global variable with a specific meaning. But as we don't have a tSetGlobalVariable, using a tContextLoad is better.

[Anonymous]

Hi,
I need to do the same with integer field. how can i do this?

[Anonymous]

Hello

I need to do the same with integer field. how can i do this?

"select * from person where id="+context.id
Best regards

shong

[_AnonymousUser]

hi

i want to execute mysql query in tool ,which components to use,the requirement is as follows
TAB1 TAB2
id name age id place
1 x 10 1 a
2 y 12 2 b
the query is : select a.id,a.name,a.age,b.place from TAB1 a,TAB2 b where a.id=b.id;
the output should be
1 x 10 a
2 y 12 b

[Anonymous]

Hi banu,
You can use tMySqlInput and tMysqlRow to execute query. In addition, tMap is also a good option if you don't execute query.
Best regards
Sabrina

[_AnonymousUser]

Hi can u show the job design i want the result from query to be loaded into database table

[Anonymous]

Hi,
To make it clear, I have designed a demo job.
I choose to use tMysqlInput to execute the simple query. tMySqlInput-->tMySqlOutput
see my screenshots
Best regards
Sabrina