IBM Mainframe Forum

I have two tables Application and Component.

I would like to find out all the applications which has more than one component.

I am trying below

SELECT A.NME_APPLICATION, COUNT(B.CDE_COMPONENT)
FROM APPLICATION A
LEFT OUTER JOIN
COMPONENT B ON
A.KEY_APPLICATION = B.KEY_COMPONENT
WHERE A.IND_SCOPE = 'Y' and COUNT(B.CDE_COMPONENT) > 1
group by A.NME_APPLICATION

but getting following error
[IBM][CLI Driver][DB2/LINUXX8664] SQL0120N Invalid use of an aggregate function or OLAP function. SQLSTATE=42903


can some one please give me correct syntax

Hello,

Which release of db2 are you using?

9.5

doesn't matter, you can't have count(*) in your WHERE clause
you need to put it in HAVING
or start messing about with subselects

You can try below query, instead of left join, use direct join and use having clause to get the results.



Let me know.. thx