Max of a Timestamp
Moderators: chulett, rschirm, roy
Max of a Timestamp
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 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
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Max of a Timestamp
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.sainath wrote: LAST_ UPDATED > MAX(LAST_UPDATED)
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.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.
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
So, it can be 'Generated' or 'User-defined' SQL whichever way you prefer it.
Thanks Craig !
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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.
A comment like that would earn you a whack upside the head from me if you were under my wing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 132
- Joined: Wed Mar 01, 2006 11:12 pm
- Location: Pune
Re: Max of a Timestamp
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: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
where LAST_ UPDATED > (select MAX(LAST_UPDATED) from table_name where <condition for yesterdays rows>)
SMB
Re: Max of a Timestamp
Once again. Something like that will always retrieve 0. It should bebaglasumit21 wrote: where LAST_ UPDATED > (select MAX(LAST_UPDATED) from table_name where <condition for yesterdays rows>)
Code: Select all
...where LAST_UPDATED > #LastRunMaxTimeStamp#
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.