DB2 count(*) over（按字段名分区）给出 -104 z/OS 版本 7

发布于 2024-08-22 11:42:43 字数 1113 浏览 7 评论 0原文

我已经简化了查询以消除潜在的复杂性，此外我还验证了这些字段是否正确。 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'

我也尝试过子查询。

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 字符、标记或子句无效或丢失。

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

原文

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'

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北音执念 2024-08-29 11:42:43

里面有什么奇怪的特殊字符可能无法打印吗？
http://www-01.ibm.com/support/docview。 wss?uid=swg1IY43009
基本上听起来像是一个奇怪的 cr/lf 或特殊字符？是否有从 *nix 到 windows 的复制粘贴？

另外，我不确定为什么你需要分区？一个团队是否会无法实现你的目标。（看起来您只是在计算符合您条件的行数）...
您的第一个查询是这样的吗？

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

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 ?

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

回复收藏 0 原文