IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-01-16T14:14:44Z by Herb_R
PWConner
PWConner
47 Posts

Pinned topic row_number ignores where clause w/ order by

‏2018-11-29T17:06:44Z | row_number select sql

select row_number() over() as rowNumber, s.*
from qSys2.sysTableStat s
where regExp_like( table_schema, 'CLOCF' );
order by data_size desc;

 

When I run the statement above to the first semicolon (without 'order by'), the row_number function works as expected. The first row is numbered 1, second is 2, etc. If I remove the semicolon and run the statement with the 'order by' clause, the first row is 55, the second is 57, the third is 137, etc. I was curious and commented out the 'where' clause and ran the statement. The 55th record without the 'where' clause was the first record in my previous results. So, the row_number ignores the 'where' clause when the 'order by' clause is used. Is this behavior normal or am I using the functions wrong?

 

System is at 7.3 and I'm using ACS Run Scripts.

 

Did you know there is a whole other DB2 for i forum here in IBM Community? I posted this issue yesterday at Forum Directory >‎ Communities category >‎ Forum: DB2 for i >‎ Topic: row_number ignores where clause w/ order by

  • PWConner
    PWConner
    47 Posts
    ACCEPTED ANSWER

    Re: row_number ignores where clause w/ order by

    ‏2018-11-30T19:01:07Z  

    I reported this problem on the midrange-l mailing list at midrange.com. No consensus as to why the query with an 'order by' clause returns row numbers that ignore the 'where' clause.

    The workaround is to build a CTE with the 'where' clause and use row_number in the select that is over the CTE.

  • PWConner
    PWConner
    47 Posts

    Re: row_number ignores where clause w/ order by

    ‏2018-11-30T19:01:07Z  

    I reported this problem on the midrange-l mailing list at midrange.com. No consensus as to why the query with an 'order by' clause returns row numbers that ignore the 'where' clause.

    The workaround is to build a CTE with the 'where' clause and use row_number in the select that is over the CTE.

  • Herb_R
    Herb_R
    1 Post

    Re: row_number ignores where clause w/ order by

    ‏2019-01-16T14:14:44Z  

    It appears that the regular expression - regExp_like - is causing the OLAP function row_number() to be applied earlier in the Execution Plan than expected. In Run SQL Scripts (I'm using 1.1.8.1), I used the blue Explain icon on the toolbar to observe this (with your original SQL and making the follow changes).

     

    If you change where regExp_like( table_schema, 'CLOCF' )  to  where table_schema like '%CLOCF%'  then you'll get the expected results (and your select runs much faster).