I am trying to design a data flow in Design Studio 9.7 by pulling data from an informix database using a sql query in SQL Query Source Operator. We have a source Informix database and a target DB2 warehouse. We want to schedule a job that pulls transactions from a transactions table that doesnt have records organised by date but rather a sequential number in the order they are input because the source systems which write to the database sometimes are offline. The only way thus to know that the transaction has not been pulled is by checking the last record we would have copied.
So what i want to do is, i want to first get the max record number from the DB2 warehouse that i will be populating then use that result a condition in the SQL query that pulls the data from the informix table. Any ideas on how i can do this using the operators in Design Studio.
Thanks in advance.
Pinned topic Using a Sql Query Result in Another SQl
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-06-25T18:52:24Z at 2012-06-25T18:52:24Z by TimB.
SystemAdmin 110000D4XK203 Posts
Re: Using a Sql Query Result in Another SQl2012-06-25T02:46:52ZThis is the accepted answer. This is the accepted answer.You can use Select Into operator in Control Flow to get the max record number and keep that value in a variable. Then use this variable in the query in your data flow to retrieve the data from informix.
TimB. 27000094532 Posts
Re: Using a Sql Query Result in Another SQl2012-06-25T18:52:24ZThis is the accepted answer. This is the accepted answer.Hi,
you could also save the result in a data station. You can use the data station in the other data flow with table source operator. But I think the proposal with the variable is the best solution.
Best regards, Tim