Conditional Replication
Posted: Wed Jul 23, 2008 9:24 am
Hi-
I need to replicate records from an Oracle table to a SQL Server table. I have the replication working with the following critera on the source OCI stage "WHERE fiscal_year = TO_CHAR (SYSDATE, 'YYYY')". Here's the issue, when the calendar year changes, there may not be any data in the table I'm replicating from until the company closes the books on the prior fiscal year. If this is the case, I need to continue to replicate from the table, but the the criteria needs to be for the prior fiscal year. Our fiscal year runs concurrent with the calendar year.
Example, when the calendar years switches from 2008 to 2009, if the query using the criteria above returns no data, then pull the data from the prior fiscal year.
Questions:
1. How do I tell if the OCI stages query has returned no rows?
2. How do I implement this type of conditional logic in DataStage?
As always, any assistance is greatly appreciated!
I need to replicate records from an Oracle table to a SQL Server table. I have the replication working with the following critera on the source OCI stage "WHERE fiscal_year = TO_CHAR (SYSDATE, 'YYYY')". Here's the issue, when the calendar year changes, there may not be any data in the table I'm replicating from until the company closes the books on the prior fiscal year. If this is the case, I need to continue to replicate from the table, but the the criteria needs to be for the prior fiscal year. Our fiscal year runs concurrent with the calendar year.
Example, when the calendar years switches from 2008 to 2009, if the query using the criteria above returns no data, then pull the data from the prior fiscal year.
Questions:
1. How do I tell if the OCI stages query has returned no rows?
2. How do I implement this type of conditional logic in DataStage?
As always, any assistance is greatly appreciated!