Page 1 of 1
Max of a Timestamp
Posted: Wed Dec 06, 2006 4:57 pm
by 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
Posted: Wed Dec 06, 2006 6:27 pm
by 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.
Posted: Wed Dec 06, 2006 7:34 pm
by 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.
Re: Max of a Timestamp
Posted: Wed Dec 06, 2006 8:25 pm
by DSguru2B
sainath wrote: 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.
Posted: Wed Dec 06, 2006 8:30 pm
by I_Server_Whale
DSguru2B wrote: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.
Posted: Wed Dec 06, 2006 8:33 pm
by 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.
Posted: Wed Dec 06, 2006 8:37 pm
by 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.
Posted: Wed Dec 06, 2006 8:47 pm
by 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.
Re: Max of a Timestamp
Posted: Thu Dec 07, 2006 3:26 am
by baglasumit21
sainath wrote: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>)
Re: Max of a Timestamp
Posted: Thu Dec 07, 2006 8:33 am
by DSguru2B
baglasumit21 wrote:
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.