How to use Group by and Having in a SQL query in NiFi ?

Olivia_4692 — Wed, 21 Sep 2022 05:54:01 GMT

Hi All,

I have been trying to using group by with having clause in the ExecuteSQL processor but it doesn't seem to be returning the count.

The SQL query looks like :

select id_num, count(*) from xyz

where id_num<>0

group by id_num

having count(*)>1

I put this in the SQL select query. I just get an empty array. How do I use group by and having in the select query in Nifi? A normal query without these clauses works perfectly but I need a count to check for duplicates in the database.

Thank you in Advance

Re: How to use Group by and Having in a SQL query in NiFi ?

Olivia_4692 — Fri, 23 Sep 2022 05:20:49 GMT

I just realised that NiFi only supports "Group by" and doesn't support "HAVING" clause. I had to put the queries in a python script and call an execute stream command processor.

question How to use Group by and Having in a SQL query in NiFi ? in Support Questions

How to use Group by and Having in a SQL query in NiFi ?

Re: How to use Group by and Having in a SQL query in NiFi ?