CODEWITHSUNDEEP
sqldb2
CaiNiaoCoder
CaiNiaoCoder

Reputation: 3319

What's wrong with this DB2 SQL Query?

SELECT
    getOrgName(BC.ManageOrgID),
    COUNT(CASE WHEN (EXISTS (SELECT FO.OBJECTNO FROM FLOW_OBJECT FO WHERE FO.ObjectNo=CR.SerialNo) AND NVL(CR.FinallyResult,'') IN ('01','02','03','04','05')) THEN BC.ManageOrgID ELSE NULL END) 
FROM
    BUSINESS_CONTRACT BC,
    CLASSIFY_RECORD CR
WHERE
 CR.ObjectType='BusinessContract'
AND CR.ObjectNo=BC.SerialNo
GROUP BY BC.ManageOrgID, CR.SerialNo, CR.FinallyResult

The error message I receive is:

11:01:32 [SELECT - 0 row(s), 0.000 secs] [Error Code: -112, SQL State: 42607] DB2 SQL Error: SQLCODE=-112, SQLSTATE=42607, SQLERRMC=SYSIBM.COUNT, DRIVER=3.57.82 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Upvotes: 0

Views: 3557

Answers (2)

Fred Sobotka
Fred Sobotka

Reputation: 5332

Here is one way to rework the query:

SELECT
getOrgName( BC.ManageOrgID ),
COUNT( FO.ObjectNo ) AS objectcount
FROM BUSINESS_CONTRACT BC
INNER JOIN CLASSIFY_RECORD CR
    ON CR.ObjectNo = BC.SerialNo
    AND CR.ObjectType = 'BusinessContract'
    AND CR.FinallyResult IN ( '01','02','03','04','05' )
INNER JOIN FLOW_OBJECT FO
    ON FO.ObjectNo = CR.SerialNo
GROUP BY BC.ManageOrgID
;

Upvotes: 0

Michael Sharek
Michael Sharek

Reputation: 5069

"The operand of the column function name (in your case, count) includes a column function, a scalar fullselect, or a subquery." DB2 doesn't allow this. See the documentation on SQL112 for more.

I'm not really sure how to fix your query but perhaps you can try the HAVING clause after GROUP BY.

Upvotes: 2

Related Questions