Comparing with max timestamp

[hema177]

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

[DSguru2B]

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)

[hema177]

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

[thumsup9]

select * from TABLE1
where timestampColumn > #PullDate#

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

[thumsup9]

select * from TABLE1
where timestampColumn > #PullDate#

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

parameter value should be currentdate-1

[ray.wurlod]

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.

[kumar_s]

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