Conditional Replication

[msacks]

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!

[satya99]

1. Select count(*) from table_name;

[ray.wurlod]

2. Using a job sequence.

[msacks]

Ray-

So lets say the first job in the Job Sequence does a record count for the current year, and the record count comes back zero. How do I get that value and conditionally execute another stage?

[ray.wurlod]

Use a server job (since there's only one row) - the overheads are much smaller. Put the returned count into that job's user status area (search the forum for techniques), and retrieve it as the $UserStatus activity variable from the Job activity that ran that job.