Mohit, MSSQL seems to have the only parser (of the more popular DBs) that complains about an empty ORDER BY clause when doing paging (with OFFSET / FETCH NEXT), so the default behavior is to throw an exception. This happens when you don't supply a Max Value Column, meaning you want to fetch all rows but haven't specified how to order them, to ensure that each page gets unique rows and all rows are returned. Otherwise each fetch could grab duplicate rows and some rows may never be fetched, as the ordering is arbitrary. For some reason other DBs (PostgreSQL, Oracle) lets you try it with the documented caveat that the ordering is arbitrary.
So an ordering must be applied, and to that end we added a Custom ORDER BY Clause property [1] such that the user can provide their own clause if no Max Value Column is specified. This property will be available in the upcoming NiFi 1.10.0 release. The only workarounds I know of are to use QueryDatabaseTable instead of GenerateTableFetch, or to supply a Max Value Column property value in GenerateTableFetch. Regards, Matt [1] https://issues.apache.org/jira/browse/NIFI-6348 On Thu, Aug 8, 2019 at 6:18 AM Mohit Jain <[email protected]> wrote: > > Hi Team, > > I'm facing the following issue while using MS SQL 2012+. in Nifi 1.6.0 - > > GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] > GenerateTableFetch[id=87d43fae-5c02-1f3f-a58f-9fafeac5a640] failed to process > session due to Order by clause cannot be null or empty when using row paging; > Processor Administratively Yielded for 1 sec: > java.lang.IllegalArgumentException: Order by clause cannot be null or empty > when using row paging > > It seems like a bug to me, is it resolved in the latest versions of Nifi? > > Thanks, > Mohit > > > > >
