DSXchange

Hi,

I am trying to do incremental loads. I need to select the max timestamp from the target table, compare it with a timestamp value in the Source table and select all rows which are greater than the max timestamp.

I am trying to use the lookup stage in parallel to do this but need some suggestions on how to go about the same.

Thanks in advance,

Hema

If both the tables are in the same schema then you can do it in one database stage itself.
Something like

DSguru2B wrote:If both the tables are in the same schema then you can do it in one database stage itself.
Something like

Code: Select all

select * from TABLE1 
where timestampColumn > (select Max(Timestamp) from TABLE2)

Thanks for the response. Actually, I am trying to do incremental loads from an audit table which has thousands of rows added to it everyday. So if I run the job today it should only pick up those rows which were added after yesterday's load. So the logic is timestampcol > max(yesterday's timestamps). I can't figure out how to incorporate this into Datastage. Any ideas?

One more thing, I want to run this query from a user defined sql file. But that option doesnt seem to work in datastage. Is this a bug ?

Cheers

Hema

select * from TABLE1
where timestampColumn > #PullDate#

and you can always pass the current date to your parameter.

thumsup9 wrote:select * from TABLE1
where timestampColumn > #PullDate#

and you can always pass the current date to your parameter.

parameter value should be currentdate-1

Which database and which database stage are you using?

You could certainly use an SQL file from a shell script executed from an Execute Command activity in a job sequence.

What stopping you from using the Userdefined SQL query, and updating the Audit table after the job.