Re: row_number ignores where clause w/ order by

[Charles Wilt <charles.wilt@xxxxxxxxx>]

To clarify...

I said I suspected it was REGEXP_LIKE() in conjunction with the UDTF...

Might be a bug, might be WAD, you'd need to ask IBM.

Charles

On Fri, Nov 30, 2018 at 7:17 AM Patrick Conner via MIDRANGE-L <
midrange-l@xxxxxxxxxxxx> wrote:

> Jack,
>
>
> Thanks. Charles provided a similar statement earlier. And I get the CTE
> works.
>
> But I'm still curious that the row_number function works differently based
> on the 'order by' clause. Charles mentioned earlier that he suspects the
> regExp_like is the culprit, but regExp_like is in the statement that
> numbers only the resulting records and it is in the statement that numbers
> the records before applying the 'where' clause.
> If someone can provide an explanation, I'm listening. Otherwise, I've
> moved on with the CTE.
>
>
>
> Thanks to the mailing list for everyone's help
>
>
>
> -----Original Message-----
> From: Jack Callahan <jjcllhn@xxxxxxxxx>
> To: midrange-l <midrange-l@xxxxxxxxxxxx>
> Sent: Fri, Nov 30, 2018 8:41 am
> Subject: RE: row_number ignores where clause w/ order by
>
> I think this works the way you expect (row numbers assigned by descending
> size for the selected tables:
>
> With SelectTables AS
> (select *
> from qSys2.sysTableStat
> where regExp_like( table_schema, 'JJCWORK' )
> order by data_size desc
> )
> SELECT  row_number() over(order by data_size desc) as rowNumber,s.* from
> SelectTables s;
> --
> 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
>
> --
> 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