Need a help on query



IBM's flagship relational database management system

Need a help on query

Postby aditya.vartak » Wed Jun 30, 2010 5:33 pm

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
aditya.vartak
 
Posts: 8
Joined: Wed Jun 09, 2010 12:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need a help on query

Postby dick scherrer » Wed Jun 30, 2010 11:05 pm

Hello,

Which release of db2 are you using?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: Need a help on query

Postby aditya.vartak » Thu Jul 01, 2010 2:32 pm

9.5
aditya.vartak
 
Posts: 8
Joined: Wed Jun 09, 2010 12:16 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need a help on query

Postby GuyC » Thu Jul 01, 2010 6:26 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Need a help on query

Postby cdhami » Sun Jul 04, 2010 8:10 pm

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

SELECT A.NME_APPLICATION, COUNT(B.CDE_COMPONENT)
FROM APPLICATION A
        , COMPONENT B
WHERE A.KEY_APPLICATION = B.KEY_COMPONENT
AND A.IND_SCOPE = 'Y'
GROUP BY A.NME
HAVING COUNT(B.CDE_COMPONENT) >1


Let me know.. thx
cdhami
 
Posts: 1
Joined: Sun Jul 04, 2010 8:03 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post