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