时间戳

首页 > 解决方案 > DB2 中的查询问题——无法找到原因

sql - DB2 中的查询问题——无法找到原因

问题描述

我试图在 Db2 中执行以下查询，它给了我一些错误，我无法确定根本原因。有人可以在这里帮忙。

with
  test as(
  select * from (
    select
      ID,SOURCE,NUMBERD
     from TABLE where RND='4')t (ID,SOURCE,NUMBERD)
     ),
  t as (
    select
      ID,
      count(*) qnt,
      count(distinct SOURCE) distinct_qnt,
      count(distinct NUMBERD) NUMBERD_CNT,
      sum(case when SOURCE = 'EXTERNAL' then 1 else 0 END) external_qnt,
      sum(case when SOURCE <> 'EXTERNAL' then 1 else 0 END) internal_qnt
    from test
    group by ID
  )

    delete from TABLE where RND='4' and ID in(
    select ID from(
    select
    ID,
    case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
    when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END scenario_id
    from t where case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
            when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END in(2,3))asd)

执行上述查询时出现的错误是

DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=delete from;by qsMatchSetID ) ;<from>, DRIVER=3.58.81

但是，如果我单独评论删除部分并执行查询，它工作正常，如下所示。

        with
  test as(
            ...............
            ...........
                from test
        group by ID
      )
    --delete from TABLE where RND='4' and ID in(
    select ID from(
    select
    ID,
    case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
    when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END scenario_id
    from t where case when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
            when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 END in(2,3))
            ---asd)

标签： sqldb2

解决方案

如果您想将 CTE 用于数据更改语句，您可以使用subselect的 data-change-table-reference 。

declare global temporary table session.table (rnd varchar(10), id int, SOURCE varchar(10), NUMBERD int) 
with replace on commit preserve rows not logged;

with
  test as
(
  select * 
  from 
  (
    select ID, SOURCE, NUMBERD
    from SESSION.TABLE 
    where RND='4'
  ) t (ID,SOURCE,NUMBERD)
 )
 , t as 
 (
    select
      ID,
      count(*) qnt,
      count(distinct SOURCE) distinct_qnt,
      count(distinct NUMBERD) NUMBERD_CNT,
      sum(case when SOURCE = 'EXTERNAL' then 1 else 0 END) external_qnt,
      sum(case when SOURCE <> 'EXTERNAL' then 1 else 0 END) internal_qnt
    from test
    group by ID
)

SELECT COUNT(1)
FROM OLD TABLE 
(
  delete from SESSION.TABLE 
  where RND='4' and ID in
  (
    select ID 
    from
    (
    select
      ID
    , case 
        when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
        when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 else 1 
      END scenario_id
    from t 
    where 
      case 
        when (external_qnt = 0 or external_qnt = qnt or NUMBERD_CNT >1) then 3
        when (external_qnt = 1 and distinct_qnt = qnt and internal_qnt=0) then 2 
        else 1 
      END in (2,3)
    ) asd
  )
);

推荐阅读