Re: row_number ignores where clause w/ order by

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Patrick...

Sorry, think I misread your original post...and I suspect your reply has a
typo as what you've actually posted would be a syntax error.

In answer to your actual question...

The code you posted doesn't need REGEXP_LIKE(), this will give the same
rows, but with the row_numbers() you'd expect.
select row_number() over(order by data_size desc) as rowNumber, s.*
from qSys2.sysTableStat s
where  table_schema = 'CLOCF'

If you really need REGEXP_LIKE(). you'd probably want to build a CTE

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

note: huge performance improvement by using the first form :)

Now as to what's going on...
My first thought was the REGEXP_LIKE() was the only culprit...

But testing REGEXP_LIKE() on a physical table doesn't have the same issues.

Thus, the full answer seems to be that standard SQL comparisons like '=' or
'LIKE', can be pushed down into the UDTF that builds the qSys2.sysTableStat
view.
Whereas  REGEXP_LIKE() can't be, so the row_number() function gets applied
first probably because the view has to be instantiated first...

Looking at the Visual Explain for both forms shows you the differences.

HTH,
Charles


On Thu, Nov 29, 2018 at 12:09 PM Patrick Conner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxx> wrote:

> Charles,
>
>
> I tried:
>
>
> select row_number(order by data_size desc) over() as rowNumber, s.*
> from qSys2.sysTableStat s
> where regExp_like( table_schema, 'CLOCF' )
> ;
>
>
>
> ...and I get the same results. First record is number 55, second is 57,
> etc. The 'where' is still ignored as far as numbering the rows is
> concerned.
>
>
>
> Patrick
>
>
>
> ------------------------------
>
> message: 5
> date: Thu, 29 Nov 2018 09:56:36 -0700
> from: Charles Wilt <charles.wilt@xxxxxxxxx>
> subject: Re: row_number ignores where clause w/ order by
>
> If you want the row_number to be generated in a specific order, you need to
> include the `window-order-clause`
>
> select row_number() over (order by data_size desc)
> <snip>
>
> Charles
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com