DB2 count(*)除以(按fieldname分区)提供-104 z/OS版本7。

时间：2022-08-12 22:59:07

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.

我已经精简了查询以消除潜在的复杂性，另外我已经验证了字段是正确的。DB2 UDB zSeries V7是我的DB2版本。

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'

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.

接收的错误是om.ibm.db2.jcc.b。SqlException: DB2 SQL错误:SQLCODE: -104, SQLSTATE: 42601, SQLERRMC:(;，从SQLCODE SQLSTATE -104非法符号令牌。42601字符、令牌或子句无效或丢失。

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

任何建议吗?我无法确定我可能会犯什么语法错误。

2 个解决方案

#1

0  

are there any weird special characters in there that might not be printing? http://www-01.ibm.com/support/docview.wss?uid=swg1IY43009 basically sounds like a weird cr/lf or special char? Any copy pasting from *nix to windows ?

有没有什么奇怪的特殊字符可能没有打印出来?http://www - 01. ibm.com/support/docview.wss?uid= swg1y43009听起来像一个奇怪的cr/lf或特殊字符?从*nix到windows的任何复制粘贴?

Also, I'm not sure why you need partition by anyway? would a group by not accomplish your goal. (looks like your just counting number of rows that met your criteria)... something like this for your first query?

而且，我也不知道为什么你需要分区?一个团队会不会完成你的目标。(看起来你只是在数符合你标准的行数)……您的第一个查询是这样的吗?

SELECT 
 STDINSTRCD, 
 count(1) ,
 CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR,
 C.STDINSTRSEQ,
 1
FROM SYST.SCC004 C
WHERE  C.STDINSTRCD = '098'
group by 
STDINSTRCD, 
CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR,
C.STDINSTRSEQ,
1

#2

0  

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 Version 7对于z/OS不支持OLAP函数，也不支持row_number()。您需要重写查询以避免使用这些函数。它们是在后来的Db2版本中实现的。也可以通过这个链接查看其他人的建议。

#1

0  

are there any weird special characters in there that might not be printing? http://www-01.ibm.com/support/docview.wss?uid=swg1IY43009 basically sounds like a weird cr/lf or special char? Any copy pasting from *nix to windows ?

有没有什么奇怪的特殊字符可能没有打印出来?http://www - 01. ibm.com/support/docview.wss?uid= swg1y43009听起来像一个奇怪的cr/lf或特殊字符?从*nix到windows的任何复制粘贴?

Also, I'm not sure why you need partition by anyway? would a group by not accomplish your goal. (looks like your just counting number of rows that met your criteria)... something like this for your first query?

而且，我也不知道为什么你需要分区?一个团队会不会完成你的目标。(看起来你只是在数符合你标准的行数)……您的第一个查询是这样的吗?

SELECT 
 STDINSTRCD, 
 count(1) ,
 CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR,
 C.STDINSTRSEQ,
 1
FROM SYST.SCC004 C
WHERE  C.STDINSTRCD = '098'
group by 
STDINSTRCD, 
CAST(STDINSTRDESC AS VARCHAR(1000)) AS INSTR,
C.STDINSTRSEQ,
1

#2

0  

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 Version 7对于z/OS不支持OLAP函数，也不支持row_number()。您需要重写查询以避免使用这些函数。它们是在后来的Db2版本中实现的。也可以通过这个链接查看其他人的建议。

标签：

相关文章