HAVING functionality in IBM Query/400?

[Viking]

I don't use QUERY/400 very often but I'm trying to make one that should accomplish what this SQL statement does:

select zy$gnr, count(*)
from f58330
where zy$ccp = 'R' and zy$roc = 'R'
group by zy$gnr
having count(*) > 1
order by zy$gnr

I can create everything above in a query, except I don't know how to handle the HAVING clause, i.e. to only get those that have a count > 1. Can I accomplish this in Query/400?

[Brian Rusch]

I don't think you would be able to do this within a single query. You would have to run 2 queries, the first to output a temporary file defining all of the elements except the having clause and the second to select the records that match the having clause criteria.

[tomliotta]

I have to ask why you might do it with Query/400 since a QM query can do it easily. (A formatted report might be a reason if the SQL DevKit isn't available, but an appropriate QM report form could be extracted from the Query/400 query with RTVQMFORM.)

[Viking]

Tom, our users have a utility that lets them specify a query name and library, and then it will run the query and return the results into an Excel sheet without me having to do anything. So sometimes it's a quick and easy way to let them access some data and get it into Excel on their own, if the data can be processed/retrieved with a Query/400 query.