GROUP BY Error

[RhythmAddict112]

Hi,
I'm trying to execute a query against DB2 (7.2 I believe) and it's throwing me the following exception...

"[IBM][CLI Driver][DB2/NT] SQL0119N An expression starting with "DESCRIPTION" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQLSTATE=42803"

My query looks as follows:

SELECT 
SUM(CASE WHEN STATUSCD  = 'CO' THEN APPESTIMATE*.05 ELSE NULL END)AS "COMPLETE",
SUM(CASE WHEN STATUSCD = 'AN' THEN APPESTIMATE*.20 ELSE NULL END) AS "ANALYSIS",
SUM(CASE WHEN STATUSCD = 'RF' THEN APPESTIMATE*.20 ELSE NULL END) AS "DESIGN",
SUM(CASE WHEN STATUSCD = 'EX' THEN APPESTIMATE*.40 ELSE NULL END) AS "CODE",
SUM(CASE WHEN STATUSCD = 'TS' THEN APPESTIMATE*.05 ELSE NULL END) AS "TEST",
SUM(CASE WHEN STATUSCD = 'UA' THEN APPESTIMATE*.10 ELSE NULL END) AS "UAT",
SUM(CASE WHEN STATUSCD = 'RS' THEN APPESTIMATE*.20 ELSE NULL END) AS "REQUIREMENTS",
"DEPARTMENTGRP"."DESCRIPTION"
FROM   "PRTADMIN"."APPLICATIONLOE" "APPLICATIONLOE" 
INNER JOIN (((("PRTADMIN"."PROJECTMAIN" "PROJECTMAIN" 
INNER JOIN "PRTADMIN"."PROJECTAPP" "PROJECTAPP" ON "PROJECTMAIN"."PROJECTID"="PROJECTAPP"."PROJECTID") 
INNER JOIN "PRTADMIN"."APPLICATION" "APPLICATION" ON "PROJECTAPP"."APPLICATIONCD"="APPLICATION"."APPLICATIONCD") 
INNER JOIN "PRTADMIN"."STATUS" "STATUS" ON "PROJECTAPP"."APPSTATUSCD"="STATUS"."STATUSCD") 
INNER JOIN "PRTADMIN"."STATUSGROUP" "STATUSGROUP" ON "STATUS"."STATUSGROUPCD"="STATUSGROUP"."STATUSGROUPCD") ON "APPLICATIONLOE"."APPLICATIONCD"="APPLICATION"."APPLICATIONCD"
INNER JOIN "PRTADMIN"."APPLICATIONGRP" "APPLICATIONGRP" ON "APPLICATION"."APPLICATIONGRPCD" = "APPLICATIONGRP"."GROUPCD"
INNER JOIN "PRTADMIN"."DEPARTMENTGRP" "DEPARTMENTGRP" ON "APPLICATIONGRP"."DEPTGROUPCD" = "DEPARTMENTGRP"."DEPTGROUPCD"
WHERE  "APPLICATION"."TARGETLOE" IS  NOT  NULL  
AND "APPLICATIONLOE"."YEAR"=2006 
AND "PROJECTMAIN"."ARCHIVEIND"='N' 
AND ("PROJECTMAIN"."PROJECTREQUESTCD"=1 OR "PROJECTMAIN"."PROJECTREQUESTCD"=2) 
AND ("STATUSGROUP"."STATUSGROUPCD"='IQ' OR "STATUSGROUP"."STATUSGROUPCD"='PA' OR "STATUSGROUP"."STATUSGROUPCD"='PP' OR "STATUSGROUP"."STATUSGROUPCD"='CO')
GROUP BY "DESCRIPTION"
ORDER BY "DESCRIPTION"

I'm not sure where I'm going wrong here. If I eliminate the "DEPARTMENTGRP"."DESCRIPTION" from the SELECT clause, it works fine... Any help would be appreciated as this is getting a bit frustrating. Also, is there a way for me to sub select from the above query? Ie, run the following...

select distinct "DEPARTMENTGRP"."DESCRIPTION" FROM (
SELECT 
SUM(CASE WHEN STATUSCD  = 'CO' THEN APPESTIMATE*.05 ELSE NULL END)AS "COMPLETE",
SUM(CASE WHEN STATUSCD = 'AN' THEN APPESTIMATE*.20 ELSE NULL END) AS "ANALYSIS",
SUM(CASE WHEN STATUSCD = 'RF' THEN APPESTIMATE*.20 ELSE NULL END) AS "DESIGN",
SUM(CASE WHEN STATUSCD = 'EX' THEN APPESTIMATE*.40 ELSE NULL END) AS "CODE",
SUM(CASE WHEN STATUSCD = 'TS' THEN APPESTIMATE*.05 ELSE NULL END) AS "TEST",
SUM(CASE WHEN STATUSCD = 'UA' THEN APPESTIMATE*.10 ELSE NULL END) AS "UAT",
SUM(CASE WHEN STATUSCD = 'RS' THEN APPESTIMATE*.20 ELSE NULL END) AS "REQUIREMENTS",
"DEPARTMENTGRP"."DESCRIPTION"
FROM   "PRTADMIN"."APPLICATIONLOE" "APPLICATIONLOE" 
INNER JOIN (((("PRTADMIN"."PROJECTMAIN" "PROJECTMAIN" 
INNER JOIN "PRTADMIN"."PROJECTAPP" "PROJECTAPP" ON "PROJECTMAIN"."PROJECTID"="PROJECTAPP"."PROJECTID") 
INNER JOIN "PRTADMIN"."APPLICATION" "APPLICATION" ON "PROJECTAPP"."APPLICATIONCD"="APPLICATION"."APPLICATIONCD") 
INNER JOIN "PRTADMIN"."STATUS" "STATUS" ON "PROJECTAPP"."APPSTATUSCD"="STATUS"."STATUSCD") 
INNER JOIN "PRTADMIN"."STATUSGROUP" "STATUSGROUP" ON "STATUS"."STATUSGROUPCD"="STATUSGROUP"."STATUSGROUPCD") ON "APPLICATIONLOE"."APPLICATIONCD"="APPLICATION"."APPLICATIONCD"
INNER JOIN "PRTADMIN"."APPLICATIONGRP" "APPLICATIONGRP" ON "APPLICATION"."APPLICATIONGRPCD" = "APPLICATIONGRP"."GROUPCD"
INNER JOIN "PRTADMIN"."DEPARTMENTGRP" "DEPARTMENTGRP" ON "APPLICATIONGRP"."DEPTGROUPCD" = "DEPARTMENTGRP"."DEPTGROUPCD"
WHERE  "APPLICATION"."TARGETLOE" IS  NOT  NULL  
AND "APPLICATIONLOE"."YEAR"=2006 
AND "PROJECTMAIN"."ARCHIVEIND"='N' 
AND ("PROJECTMAIN"."PROJECTREQUESTCD"=1 OR "PROJECTMAIN"."PROJECTREQUESTCD"=2) 
AND ("STATUSGROUP"."STATUSGROUPCD"='IQ' OR "STATUSGROUP"."STATUSGROUPCD"='PA' OR "STATUSGROUP"."STATUSGROUPCD"='PP' OR "STATUSGROUP"."STATUSGROUPCD"='CO')
--GROUP BY "DEPARTMENTGRP"."DESCRIPTION","PROJECTAPP"."APPESTIMATE","STATUS"."STATUSCD"
--ORDER BY "DEPARTMENTGRP"."DESCRIPTION","PROJECTAPP"."APPESTIMATE","STATUS"."STATUSCD"
GROUP BY "DESCRIPTION"
ORDER BY "DESCRIPTION")

As of now, the subselect throws the following exception..
"[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "ER BY "DESCRIPTION")". Expected tokens may include: ")". SQLSTATE=42601"

Sorry, a bit new to Db2

All hail the INTERWEB!

 

[notadba]

Without really looking at the code for syntax - here is something you could try. DB2 has a real hard time dealing with with 'LONG' field types when performing aggregate functions. You could try to substr your description field to 30 characters and see if that works.

 

[RhythmAddict112]

I should mention...I got this to work just after I posted. Not by changing my query though - I actually exited my client and relaunched, and it worked. I actually even cut and pasted the query directly from this post (!??) Go figure....

All hail the INTERWEB!