Max of a Timestamp

[sainath]

Hi
I am using oci stage at input and LAST_UPDATED(Timestamp) as one of the column.
I want to capture only records whose LAST_ UPDATED > MAX(LAST_UPDATED)

I am tring to use ICONV AND OCONV functions . but i want to know if there is any other approach.
thks

[I_Server_Whale]

Hi,

Your post is not clear as to what you are trying to achieve. I'm thinking you want to do a incremental pull from your source table? Is that what you are trying to do?

Thanks,
Whale.

[ray.wurlod]

Why not just do it in the SQL?

Iconv() and Oconv() do not work with timestamps; you have to break the timestamp down into date and time components to work with these functions.

[DSguru2B]

LAST_ UPDATED > MAX(LAST_UPDATED)

If you use that statement, even in sql, it will always return 0 records as your retrieving records with timestamp greater than the max timestamp.

[I_Server_Whale]

If you use that statement, even in sql, it will always return 0 records as your retrieving records with timestamp greater than the max timestamp.

That's absolutely true. I thought the same. But you can store the MAX(LAST_UPDATED) from yesterday's into a job parameter and then use this job parameter in your user-defined SQL statement of the OCI stage for a incremental pull if that's what you are trying to achieve.

Whale.

[chulett]

Of course. And why the fascination with User Defined SQL peoples? Nothing about using a parameter in a where clause requires user defined sql.

[I_Server_Whale]

Sorry! My bad. Been doing a lot of user-defined SQL at the current site. So, just got carried away.

So, it can be 'Generated' or 'User-defined' SQL whichever way you prefer it.

Thanks Craig !

Whale.

[chulett]

Sorry - it's just a Pet Peeve of mine. I see way too many problems from a constant use of User-Defined SQL for no good reason. Mostly what I hear is it is 'easier' because it can be pasted from your query tool of choice. That or 'it makes it easier to read'. The perils of that approach are legion.

A comment like that would earn you a whack upside the head from me if you were under my wing.

[baglasumit21]

Hi
I am using oci stage at input and LAST_UPDATED(Timestamp) as one of the column.
I want to capture only records whose LAST_ UPDATED > MAX(LAST_UPDATED)

I am tring to use ICONV AND OCONV functions . but i want to know if there is any other approach.
thks

Try using a sub query in the where clause of the source stage to get the records with LAST_ UPDATED > MAX(LAST_UPDATED) for eg:

where LAST_ UPDATED > (select MAX(LAST_UPDATED) from table_name where <condition for yesterdays rows>)

[DSguru2B]

where LAST_ UPDATED > (select MAX(LAST_UPDATED) from table_name where <condition for yesterdays rows>)

Once again. Something like that will always retrieve 0. It should be

Code: Select all

...where LAST_UPDATED > #LastRunMaxTimeStamp#

or if your storing the Max timestamp of previous run in some other table then the subquery will be from that table, but not from the same table.