last record

kumar_j · Post by **kumar_j** » Fri Feb 03, 2006 12:30 am

hi all,
how to get the last record in a file/database

advanced thks

ArndW · Post by **ArndW** » Fri Feb 03, 2006 12:36 am

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.

ameyvaidya · Post by **ameyvaidya** » Fri Feb 03, 2006 4:45 am

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:

Code: Select all

@INROWNUM=<reflink>.count

will identify the last row.

rwierdsm · Post by **rwierdsm** » Fri Feb 03, 2006 3:22 pm

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.

Actually, this doesn't work in Oracle. It determines the last row before the sort. You have to use other, more arcane methods.

Lookup the Rank command if you need to do this in Oracle.

Rob W

ArndW · Post by **ArndW** » Fri Feb 03, 2006 3:31 pm

Rob,

I'm not sure what you meant by your post, if I want the "first" row on a column such as an integer one called "AGE" and do a select with an "ORDER BY AGE" in my DataStage query the first row returned will have the lowest column value of "AGE".

rwierdsm · Post by **rwierdsm** » Fri Feb 03, 2006 3:39 pm

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

chulett · Post by **chulett** » Fri Feb 03, 2006 5:00 pm

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.

rwierdsm · Post by **rwierdsm** » Sat Feb 04, 2006 7:37 am

I remember now, it was a lookup, not a data source, that this applied to. Of course, only one row can be returned in a lookup. If only the first of multiple rows is required, Oracle will do the sort after the rownum = 1 psuedo column.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 04, 2006 2:55 pm

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.