Nifi QueryDatabaseTable Oracle Custom Query Alias issue

[#1]

My Source DB is Oracle. I have a custom query which joins 3 tables, TableA TableB and TableC.

TableA -

rollno, department, college, pincode, IS_Y_N

TableB -

rollno, name, email

TableC -

slno, pincode

The Query is as follows

select RTable.*,  from (select CASE WHEN `A`.`IS_Y_N` = '0' then 'FALSE' Else 'TRUE'  END AS `IS_Y_N`, 
A.department, A.college, A.pincode, B.name, B.email from TableA A JOIN TableB B on A.rollno = B.rollno) RTable JOIN TableC C ON RTable.pincode = C.pincode

I put this query as Custom Query in QueryDatabaseTable processor with Oracle as DatabaseType and TableA as TableName (which is mandatory). When starting the processor, the resultant query will be

SELECT * FROM (--ABOVE_QUERY--) AS TableA.

This 'AS TableA' (aliasing) syntactically doesn't support in Oracle DB. But it is working fine in MySQL. Found out that the resultant query generated by Nifi is by appending the strings "SELECT * FROM("+query+") AS "+tableName. Aliasing is not working for Oracle DB. Is there any workaround for this? Please help.

Creating a view in Oracle DB and giving the view name in QueryDatabaseTable is also a solution. But I dont want to create a view in the source DB.

[#2 · Accepted solution]

Oracle has different syntax for aliasing columns (i.e. use "AS") versus tables (i.e. don't use "AS"). The existing code in 1.7.0 hardcodes the "AS" keyword. I have written NIFI-5471 to delegate the generation of the table alias clause to the database adapter. Unfortunately I am not aware of any workaround.

[#3]

Thanks Matt