DB2计数（*）超过（按字段名称划分）给出-104 z / OS版本7 [英] DB2 count(*) over(partition by fieldname) giving -104 z/OS version 7

问题描述

我简化了查询以消除潜在的复杂性，此外，我还验证了字段正确。 DB2 UDB zSeries V7是我的db2版本。

I have slimmed down the query to remove potential complications, in addition I have verified that the fields are correct. DB2 UDB zSeries V7 is my db2 version.

SELECT 
    STDINSTRCD, 
    COUNT(*) OVER(PARTITION BY STDINSTRCD),
    CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR,
    C.STDINSTRSEQ,
    1
FROM 
    SYST.SCC004 C
WHERE  
    C.STDINSTRCD = '098'

我也尝试了一个子查询。

I have tried a subquery as well.

select 
 H2.FRSTSTDINSTRCD,
 (select count(*) from SYST.scC004 Ci where '098'=Ci.STDINSTRCD) as cnt, 
 cast(STDINSTRDESC as varchar(1000)),
 C.STDINSTRSEQ,
 1
from SYST.scE4A00 H2
 LEFT OUTER JOIN SYST.scC004 C
 ON C.STDINSTRCD = H2.FRSTSTDINSTRCD
 WHERE
  H2.CTLENTYID='MCS'
  AND H2.VCKVAL='12654'
  AND H2.POKVAL='0198617S12 000  000'

错误为om.ibm.db2 .jcc.b.SqlException：DB2 SQL错误：SQLCODE：-104，SQLSTATE：42601，SQLERRMC：（;，FROM INTO sqlcode sqlstate
-104非法符号令牌。
42601一个字符，令牌或子句无效或丢失。

The error is receive is om.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: (;, FROM INTO sqlcode sqlstate -104 Illegal Symbol token. 42601 A character, token, or clause is invalid or missing.

有什么建议吗？我一直无法确定我可能犯什么语法错误。

Any advice? I have been unable to determine what syntax error I might me making.

推荐答案

z / OS的Db2版本7不支持OLAP函数或row_number（）。您需要重写查询以避免使用此类功能。他们到达了更高版本的Db2。另请参见其他人通过此链接。

Db2 Version 7 for z/OS does not support OLAP functions, or row_number(). You need to rewrite your query to avoid using such functions. They arrived in later Db2 versions. See also other people's tips on alternatives via this link.

这篇关于DB2计数（*）超过（按字段名称划分）给出-104 z / OS版本7的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！