Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Post Unanswered: ROW_NUMBER Query giving error

    Hello,

    I need a query which gives the row number and uses the row number (row_next in this case) in the WHERE statement. My Query is as such but it is giving me an error:


    SELECT ROW_NUMBER() OVER () AS ROW_NEXT
    FROM MY_TABLE
    WHERE 1 = 1
    AND FIELD1 = 'Y'
    AND ROW_NEXT BETWEEN 1 AND 20


    The error is:
    16:33:55 [SELECT - 0 row(s), 0.000 secs] 1) [Error Code: -206, SQL State: 42703] "ROW_NEXT" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.57.82. 2) [Error Code: -727, SQL State: 56098] An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-206", SQLSTATE "42703" and message tokens "ROW_NEXT".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.57.82


    Can somebody help me with this query?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ashvin203, ROW_NUMBER () is processed with the SELECT which is the last think processed. So at the time the WHERE clause is processed, ROW_NEXT doesn't exist. Put the statement in a Nested table:

    Code:
    SELECT ROW_NEXT
    FROM (
          SELECT ROW_NUMBER() OVER () AS ROW_NEXT
          FROM MY_TABLE
          WHERE 1 = 1
            AND FIELD1 = 'Y'
         )
    WHERE ROW_NEXT BETWEEN 1 AND 20

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Unless you have an order by, the results may not be consistant from one execution to the next:
    Code:
    SELECT ROW_NEXT
    FROM (
          SELECT ROW_NUMBER() OVER (ORDER BY .....) AS ROW_NEXT
          FROM MY_TABLE
          WHERE 1 = 1
            AND FIELD1 = 'Y'
         )
    WHERE ROW_NEXT BETWEEN 1 AND 20
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2012
    Posts
    4

    ROW_NUMBER Query giving error

    SELECT ROW_NEXT
    FROM (
    SELECT ROW_NUMBER() OVER () AS ROW_NEXT
    FROM MY_TABLE
    WHERE 1 = 1
    AND FIELD1 = 'Y'
    )
    WHERE ROW_NEXT BETWEEN 1 AND 20


    The above query works BUT among the 20 records..2 records are FIELD1='Y' and 18 records are FIELD1='N'. But the resultset returned is returning me 20 records all the time whether I put FIELD1 = 'Y' or FIELD1 ='N'. Something is wrong in the query. Any idea?

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    There must be something that is not being displayed. There should be only rows where FIELD1 = 'Y' that would have ROW_NUMBER applied to them.
    Code:
    WITH TESTTAB (FIELD1)
      AS (
          SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'N' FROM SYSIBM.SYSDUMMY1
        )
    SELECT ROW_NEXT, FIELD1
    FROM (SELECT ROW_NUMBER() OVER() AS ROW_NEXT
          , FIELD1
          FROM TESTTAB
          WHERE 1 = 1
            AND FIELD1 = 'Y'
         ) AS A
    WHERE ROW_NEXT BETWEEN 1 AND 3
    
    ROW_NEXT             FIELD1
    -------------------- ------
                       1 Y     
                       2 Y     
                       3 Y
    PS Definitely agreee with Marcus_A that you should actually Order by something to get consistent results.

  6. #6
    Join Date
    Mar 2012
    Posts
    4

    Post ROW_NUMBER Query giving error

    Hi Stealth_DBA,

    I think I didnt explain to you what I need clearly.

    Actually from your example as per the query, the result that I want would be the only the row 1 and the row 3 since only these two rows have FIELD1 = 'Y'. I don't the first three 'Y' from the table. I want the 'Y' from the first 3 rows.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ashvin203 View Post
    Hi Stealth_DBA,

    I think I didnt explain to you what I need clearly.

    Actually from your example as per the query, the result that I want would be the only the row 1 and the row 3 since only these two rows have FIELD1 = 'Y'. I don't the first three 'Y' from the table. I want the 'Y' from the first 3 rows.
    I think you are confused. The row number is only assigned to the qualifying rows, not the original rows in the tables. Note in Stealth_DBA's example, there are 5 row with 'Y", but only the first 3 are returned.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Mar 2009
    Posts
    16
    Hi Ashvin,

    I think you are looking for something like this..

    Taking the Stealth_DBA's example:


    WITH TESTTAB (FIELD1)
    AS (
    SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y' FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N' FROM SYSIBM.SYSDUMMY1
    )
    SELECT ROW_NEXT, FIELD1
    FROM (SELECT ROW_NUMBER() OVER() AS ROW_NEXT
    , FIELD1
    FROM TESTTAB
    WHERE 1 = 1
    ) AS A
    WHERE ROW_NEXT BETWEEN 1 AND 3 AND FIELD1 = 'Y'

  9. #9
    Join Date
    Mar 2009
    Posts
    16
    You may need assign some order to the rows while assigning row number to get the expected output.
    Something like this can be done:

    WITH TESTTAB (FIELD1, id)
    AS (
    SELECT 'Y',1 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N',2 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y',3 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N',4 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y',5 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N',6 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y',7 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N',8 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'Y',9 FROM SYSIBM.SYSDUMMY1 UNION ALL
    SELECT 'N',10 FROM SYSIBM.SYSDUMMY1
    )
    SELECT ROW_NEXT, FIELD1 , id
    FROM (SELECT ROW_NUMBER() OVER(order by id) AS ROW_NEXT
    , FIELD1, id
    FROM TESTTAB
    WHERE 1 = 1
    ) AS A
    WHERE ROW_NEXT BETWEEN 1 AND 3 AND FIELD1 = 'Y'


    ROW_NEXT FIELD1 ID
    -------- ------ --
    1 Y 1
    3 Y 3

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The ORDER BY is extremely important. Otherwise, you could just do a
    Code:
    SELECT ...
    FROM ...
    WHERE FIELD1 = 'Y'
    FETCH FIRST 3 ROWS ONLY
    The point is that DB2 does not guarantee any kind of order when numbering rows unless the "OVER(ORDER BY ...)" is present. So it can very well process all rows with FIELD1 = 'Y' first (and those in any arbitrary order) and the rest of the query returns the first 3 of those. In that case, my query gives the same results and you can't really say it is wrong.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Mar 2012
    Posts
    4

    Thumbs up ROW_NUMBER Query giving error

    Thx Bash..this is what I wanted.

    I figured it out after Marcus example that the AND Field1 = 'Y' code should be last.

    Many thanks buddies. This was my first post on this forum and indeed the forum is quick and helpful

Tags for this Thread

Posting Permissions

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