I have a flow where i am querying our DB and the DBA would like me to add WITH UR to the end of the query. In nifi when i look at the query generated the additional where clause i have is being added in parentheses and when i put WITH UR in that field it breaks the query. Do you know if Nifi does the uncommited read automatically against the Table or if it is possible to add the clause? Thanks!
Starting from NiFi-1.7 querydatabasetable processor will allow custom query property
|Custom Query||A custom SQL query used to retrieve data. Instead of building a SQL query from other properties, this query will be wrapped as a sub-query. Query must have no ORDER BY statement.|
Supports Expression Language: true (will be evaluated using variable registry only)
This will resolve your issue i believe..!!
If you are not using NiFi-1.7 then possible work around would be using ExecuteSQL processor and storing the state in Distribute cache as described in this link.
QueryDatabaseTableRecord processor also available starting from NiFi-1.8 version.