last record
Moderators: chulett, rschirm, roy
last record
hi all,
how to get the last record in a file/database
advanced thks
how to get the last record in a file/database
advanced thks
A database doesn't really have a "last" record - at least not until you give it sort criteria. If you have the sort criteria and invert it your 1st record is the one you want. Sequential files need to be read to the end in DataStage jobs to get the last record. You could use the UNIX "tail" command with options to get the last record and use that in a job.
If you could explain how/why you need the last record you might get a better answer.
If you could explain how/why you need the last record you might get a better answer.
-
- Charter Member
- Posts: 166
- Joined: Wed Mar 16, 2005 6:52 am
- Location: Mumbai, India
If the requirement is that the last record of the stream be identified for special processing, one way is to execute a "count()" on the table or "wc -l" on the file and save the result with a dummy lookup key (value) in a hashed file. During processing, read this value in through a lookup and the expression:
will identify the last row.
Code: Select all
@INROWNUM=<reflink>.count
Amey Vaidya<i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
I am rarely happier than when spending an entire day programming my computer to perform automatically a task that it would otherwise take me a good ten seconds to do by hand.</i>
<i>- Douglas Adams</i>
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Actually, this doesn't work in Oracle. It determines the last row before the sort. You have to use other, more arcane methods.ArndW wrote:A database doesn't really have a "last" record - at least not until you give it sort criteria. If you have the sort criteria and invert it your 1st record is the one you want.
Lookup the Rank command if you need to do this in Oracle.
Rob W
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
If you are hitting Oracle with the OCI and need to get the first record based on a sort criteria, you might decide to use custom SQL to sort the data.
This fails because Oracle picks the first record BEFORE doing the sort. Have to use the RANK function.
Can't remember the exact circumstances under which I had to do this, not using Oracle at this client, but that's what we had to do.
?!?!? Perhaps I've confused the issue rather than shedding light. If so, accept my apologies!
Rob W
This fails because Oracle picks the first record BEFORE doing the sort. Have to use the RANK function.
Can't remember the exact circumstances under which I had to do this, not using Oracle at this client, but that's what we had to do.
?!?!? Perhaps I've confused the issue rather than shedding light. If so, accept my apologies!
![Wink :wink:](./images/smilies/icon_wink.gif)
Rob W
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
Rob, I think you are thinking of the ROWNUM psuedo column in Oracle, where it gets tricky to limit output based on that under some circumstances.
What Arnd is saying (I do believe) is that, with specific sort criteria - an order by - then you will have a known 'last record' in the output dataset. And if you reverse the sort, descending instead of ascending, to use the simplest example - then your last row becomes your first row. And no need for custom sql to do that.
What Arnd is saying (I do believe) is that, with specific sort criteria - an order by - then you will have a known 'last record' in the output dataset. And if you reverse the sort, descending instead of ascending, to use the simplest example - then your last row becomes your first row. And no need for custom sql to do that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you use ODBC stage you can set it to return multiple rows on a lookup, and make your own arrangements about getting the last of these. The OCI stage does not support this functionality.
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.