Accessing data from an intermediate results table

[Prof Plum]

Hi

I'm new to the forum and have a query if someone can help.

I am running on DB2 v7.

I have a master table that holds the current logical date, and another table which holds every day of the year and an indicator to determine if it is a working day (so weekends and bank holidays have this indicator set to 'N', working days are set to 'Y').

The requirement is to initiate a cleardown of other tables in the application based on a parameter defined number of working days in the past. The parameter is held on another table.

I could do this fairly easily by having a cursor that gets all the working day records that are equal to or less than the current logical date and restrict the number of rows fetched, order them in descending date and read through to the end.

However I was trying to get the data into an intermediate table and then get the lowest date using the MIN function. As soon as I bracket the sub query the syntax checker throws out the ORDER BY.

I have the following example that uses arbitrary date ranges. I have gone back 62 days to make sure that I am picking up 30 working days (which is likely to be our clear down limit)

--SELECT MIN(X.PROCESSING_DT)
--FROM (
SELECT A.PROCESSING_DT
FROM TBM004_WORKDYS A
,TBM020_MASTER B
WHERE A.PROCESSING_DT > DATE(DAYS(B.PROCESSING_DT)-62)
AND A.PROCESSING_DT <= B.PROCESSING_DT
AND A.WORK_DAY_IND = 'Y'
ORDER BY A.PROCESSING_DT DESC
FETCH FIRST 30 ROWS ONLY
--) AS X