Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: SQL0158N During Delete by ROW_NUMBER()

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    You got the paranthesis wrong ...

    Code:
    DELETE FROM
      (SELECT ROW_NUMBER() OVER(PARTITION BY USER_SID ORDER BY USER_SID)
     FROM 
      BILL.TEST_USERS) AS TU(RN)
     WHERE
      RN=1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2007
    Posts
    16
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a subquery merely produces a query result set, yes?

    why would you want to delete from a query result set?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    16
    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

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jul 2007
    Posts
    16
    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

  8. #8
    Join Date
    Jul 2007
    Posts
    16
    whoops...wrong thread...but you know, that's why they hired me...my attention to detail...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •