DB2 -聚合函数SQLCODE=-120的无效使用
DB2 -聚合函数SQLCODE=-120的无效使用
提问于 2017-02-21 09:59:55
有人能帮助我理解这个Db2查询语法有什么问题吗?由于我试图使用row_number()，它在SQLCODE-120中失败了吗？
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显示的错误:聚合函数或OLAP函数的使用无效。SQLCODE=-120，SQLSTATE=42903，DRIVER=4.18.60
回答 1
Stack Overflow用户
回答已采纳
发布于 2017-02-21 11:40:18
您有多个错误：
- 在子查询中没有定义
X。 - 在外部查询中引用
B.CREATED_DATE，但未定义它。 ROW_NUMBER()在WHERE子句中使用。
这似乎是您编写的查询的目的：
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;我不确定这个结果是否合理，但它应该能纠正你的错误。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42364150
复制相关文章
相似问题
腾讯云开发者
