• 2 replies
  • Latest Post - ‏2012-06-25T18:52:24Z by TimB.
4 Posts

Pinned topic Using a Sql Query Result in Another SQl

‏2012-06-21T14:06:06Z |
Hi experts,

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.
Updated on 2012-06-25T18:52:24Z at 2012-06-25T18:52:24Z by TimB.
  • SystemAdmin
    203 Posts

    Re: Using a Sql Query Result in Another SQl

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

    Re: Using a Sql Query Result in Another SQl


    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