Nifi QueryDatabaseTable Oracle Custom Query Alias issue

justinvtom — Fri, 27 Jul 2018 20:38:46 GMT

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.

Re: Nifi QueryDatabaseTable Oracle Custom Query Alias issue

mburgess — Tue, 31 Jul 2018 04:00:55 GMT

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.

Re: Nifi QueryDatabaseTable Oracle Custom Query Alias issue

justinvtom — Fri, 03 Aug 2018 18:45:07 GMT

Thanks Matt

question Re: Nifi QueryDatabaseTable Oracle Custom Query Alias issue in Archives of Support Questions (Read Only)

Nifi QueryDatabaseTable Oracle Custom Query Alias issue

Re: Nifi QueryDatabaseTable Oracle Custom Query Alias issue

Re: Nifi QueryDatabaseTable Oracle Custom Query Alias issue