SQL Statement

[casporov]

Dear All again
i want to write and SQL statement from java to get the last 10 records from a physical file called fileone in library LIB1
is that can be done?

SELECT Field1 FROM LIB1/fileone

thanks in advance

[BenThurley]

Re: SQL Statement

How would you do it in RPG? IMO the only reliable way is to have a timestamp field on the file. This could be auto-populated by a trigger whenever you add or update. The only drawback with this approach is it doesn't work for records that already exist in the file. However, if you only want the last ten then that shouldn't be a problem for long.

[bryce4president]

Re: SQL Statement

ResultSet.afterlast()

for(int i=1; i<=10; i++)
{
ResultSet.previous()
}

//This will put you the 10th element from the bottom. If you want to process them backwards then just do your processing after each .previous() within the for loop. If you want to process them down from there then just do another for loop like this

for(int j = 1; j >=10; j++)
{
//process the data
ResultSet.next();
}

[BenThurley]

Re: SQL Statement

Sorry, was it the last ten records added to the file or the last ten in the result set?

[bryce4president]

Re: SQL Statement

if you just do a select * from lib/file then your result set will be the file in order as it is on the machine. So the last 10 records would be the bottom 10 records, correct? Or does the file reuse rrn's in the file if a record has been hard deleted?

[BenThurley]

Re: SQL Statement

If it is then it is more by fluke than design. SQL work's on set theory which has no order unless you specify one. I'm sure I have seen this discussed here before?

[bryce4president]

Re: SQL Statement

Yes, it probably has been discussed on here. That is why I am hesitant with my solution.

Casporov, do you have a date field in the file??