Howdy DB2ers,

Riddle me this…I copied the syntax below directly from IBM.

http://publib.boulder.ibm.com/infoce...63%6c%69%22%20

DELETE FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_SID)
FROM
BILL.TEST_USERS AS TU(RN)
WHERE
RN=1
)
;

When I execute it, I get SQL0158N, SQLSTATE 42811. Searched the web and the forum but did not find anything I recognized as a solution for my particular query. What gives?

The number of column names specified must be equal to the number of columns in the result table of the associated fullselect. If name is a staging table and the associated materialized query table has group by clause, the number of column names specified must be 2 more than the number of columns in the materialized query table for which the staging table is being defined. If the associated materialized query table has no group by clause, the number of column names specified must be 3 more than the number of columns in the materialized query table.

The statement cannot be processed.

User Response:
Correct the syntax so that the list of column names defined for name specify a name for each column of the result table.

Thanks in advance for your lessons...
Bill

Server Info:
SunOS 5.9 sun4u sparc SUNW,Ultra-Enterprise

DB2 Version:
Product Name = "DB2 Enterprise Server Edition"
Version Information = "8.2"
Product Name = "DB2 High Availability Disaster Recovery Option"
Version Information = "8.2"
Product Name = "DB2 Advanced Security Option"
Version Information = "8.2"

DB2 v8.1.0.64
FixPak 7
Type ESE

You got the paranthesis wrong ...

Thanks Sath,

Now then, you got me past my syntax error, but it did the obvious and not what I intend. Do you know an elegant way to implement this logic:

DELETE FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_SID)
FROM
BILL.TEST_USERS) AS TU(RN)
WHERE
RN <=MAX(RN)-10

SQL0120N Invalid use of an aggregate function or OLAP function

DELETE FROM
(SELECT MAX(ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_SID))
FROM
BILL.TEST_USERS) AS TU(RN)
WHERE
RN <= RN-10

SQL0112N The operand of the column function "SYSIBM.MAX" includes a column
function, a scalar fullselect, or a subquery. SQLSTATE=42607

a subquery merely produces a query result set, yes?

why would you want to delete from a query result set?

My marching orders are to write a query that will preserve the ten newest rows and delete all rows "older" than those ten newest rows. This is to be CLP and not SP or UDF. I had hoped the simplest implementation would be finding the maximum row number and then delete all rows where RN is less than MAX RN-10.

BTW, thanks for fixing the parenthetical expression. The obvious escaped me again...

Bill

ORDER BY USER_SID DESC

And, then

delete RN > 10

BTW, I'm not sure why you have to partition by and order by the same column ... Is it a typo ?

How do you want to handle duplicates ? ie if there are "fifteen newest" rows ?
Have a look at RANK() and DENSE_RANK() functions as well.

HTH

Sathyaram

Thanks Sath,

Yes, it was a typo. I will look at the RANK and DENSE_RANK functions, but for now the query is working thanks to your help.

Perhaps the picture will truly be clear to you with the posting of my completed query. The goal is to preserve a configurable number of the login histories of users of an organization. The final query:

DELETE FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY ORG_USER_LOGINS_SID DESC)
FROM
ORG_USER_LOGINS
WHERE
USER_SID
IN (
SELECT DISTINCT
LOGS.USER_SID
FROM
ORGANIZATION ORG,
ORG_USER USER,
ORG_USER_LOGINS LOGS
WHERE
ORG.ORG_SID=USER.ORG_SID
AND
USER.USER_SID=LOGS.USER_SID
AND
ORG.ORG_SID = ?)
) AS LA(RN)
WHERE
RN > ?
;

Regards and appreciation,
Bill

whoops...wrong thread...but you know, that's why they hired me...my attention to detail...