DB2 - Invalid use of an aggregate function SQLCODE=-120

Question

Can anyone help me to understand what is wrong with this Db2 query syntax,it is failing with SQLCODE -120 because i am trying to use row_number()?

     SELECT COUNT(ORDER_ID) OVER() TOTAL_NO_OF_RECORDS,
        ROW_NUMBER() OVER (ORDER BY CREATED_DATE DESC) AS ROW_NUM
        FROM (
        SELECT DISTINCT A.ORDER_ID ORDER_ID,
        B.AgencyName  AS AGENCY_NAME,
        C.FirstName FIRST_NAME,
        C.LastName LAST_NAME,
        DEMOGRAPHIC.State STATE,
        A.CreatedTS CREATED_DATE,
        E.WritingTIN WRITING_TIN,
        E.ParentTIN PARENT_TIN
        FROM DBO.TABLE1 A
        INNER JOIN 
        DBO.TABLE2 TABLE2 ON TABLE2.ORDER_ID=A.ORDER_ID
        INNER JOIN 
        DBO.TABLE3 TABLE3 ON TABLE3.QuoteId=TABLE2.QuoteId 
        INNER JOIN 
        DBO.Demographic DEMOGRAPHIC ON  
DEMOGRAPHIC.DemographicId=TABLE3 .DemographicId 
        INNER JOIN 
        DBO.Agent E ON E.AgentId=DEMOGRAPHIC.AgentId
        INNER JOIN 
        DBO.User USER ON USER.WebAcctID=AGENT.WebAcctId
        INNER JOIN 
        DBO.Shopper SHOPPER ON SHOPPER.ShopperId=DEMOGRAPHIC.ShopperId
        LEFT OUTER JOIN
        DBO.Subsidy D ON D.demographicId=DEMOGRAPHIC.demographicId
        LEFT OUTER JOIN
        DBO.Employer EMPLOYER ON DEMOGRAPHIC.demographicId=EMPLOYER.demographicId
        WHERE E.WritingTIN = 'XYZ' AND E.ParentTIN = 'XYZ'
        AND DEMOGRAPHIC.State='OH'
        AND A.Status IN ('X','Y','Z')
        )AS ORDER_DETAILS
        where ROW_NUMBER() OVER (ORDER BY CREATED_DATE DESC) BETWEEN ((1*50)-50)+1 AND 1*50 ORDER BY CREATED_DATE DESC

ERROR SHOWN:Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903, DRIVER=4.18.60

When LEFT JOIN, put the right side table conditions in the ON clause to get true LEFT JOIN behavior. (When in WHERE, you'll get regular inner join result...) — jarlh, Feb 21 '17 at 10:01
@jarlh issue is not due to join..it is due to the wrong usage of aggregate function..i have just changed the query for simplicity purpose. Error : Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903 — RVR, Feb 21 '17 at 10:06
My comment was not intended to solve the issue, it's just meant to improve you joins. — jarlh, Feb 21 '17 at 10:08

Gordon Linoff · Accepted Answer · 2017-02-22T02:55:52.280

You have multiple errors:

X is not defined in the subquery.
B.CREATED_DATE is referenced in the outer query, but it is not defined.
ROW_NUMBER() is used in the WHERE clause.

This would seem to be the intention of the query you write:

SELECT COUNT(*) OVER () NO_OF_RECORDS, ROW_NUM
FROM (SELECT A.ID, B.FirstName as FIRST_NAME, B.LastName as LAST_NAME,
             MAX(B.CREATED_DATE) as CREATED_DATE,
             ROW_NUMBER() OVER (ORDER BY MAX(CREATED_DATE) DESC) AS ROW_NUM
      FROM SCHEMANAME.A A INNER JOIN 
           SCHEMANAME.B B
           ON B.ShopperId = A.ShopperId LEFT OUTER JOIN
           SCHEMANAME.C C
           ON C.demographicId = B.demographicId
      WHERE A.WritingTIN = 'XYZ' AND A.ParentTIN = 'XYZ' AND
            B.State = 'OH' AND C.Status IN ('X', 'Y', 'Z')
      GROUP BY A.ID, B.FirstName, B.LastName
     ) ORDER_DETAILS
WHERE ROW_NUM BETWEEN ((1*50)-50)+1 AND 1*50 
ORDER BY CREATED_DATE DESC;

I'm not sure that this result makes sense, but it should fix your error.

thanks for the reply. in order to simplify the query, missed to notice the mistakes. I still get different issues with the approach you mention due to the addition of GroupBy clause. I am new to Db2. Can you pls check the modified query and help me fix that and explain the solution? — RVR, Feb 21 '17 at 13:38

DB2 - Invalid use of an aggregate function SQLCODE=-120

1 Answers1