删除表(复制表)中任一条或多条数据后再次执行connect...

在删除表(复制表)中任一条或多条数据后connect by start with 就报错了,如果没有删除操作 connect by start with 使用正常。
1)建表
drop table zsm_2088;
CREATE TABLE "zsm_2088" (
"itemid" varchar(32) NOT NULL,
"tmjb" decimal(1,0) DEFAULT NULL,
"sqh" varchar(20) DEFAULT NULL,
"fjbzm" varchar(50) DEFAULT NULL,
"zsm" varchar(50) DEFAULT NULL
) ENGINE=EXPRESS REPLICATED;
2)插入数据
insert into zsm_2088(itemid,tmjb,sqh,fjbzm,zsm)
values(1,'1','001','0','1');
insert into zsm_2088(itemid,tmjb,sqh,fjbzm,zsm)
values(1,'1','002','1','2');
insert into zsm_2088(itemid,tmjb,sqh,fjbzm,zsm)
values(1,'1','003','2','3');
insert into zsm_2088(itemid,tmjb,sqh,fjbzm,zsm)
values(1,'1','004','3','4');
insert into zsm_2088(itemid,tmjb,sqh,fjbzm,zsm)
values(1,'1','005','4','5');
select * from zsm_2088;
3)connect by start with 查询数据
select '贴码级别[' || tmjb || '] ' || zsm as zsm,sqh,
'贴码级别[' || (to_number(tmjb) + 1) || '] ' || fjbzm as fjbzm
from zsm_2088
connect by fjbzm = prior zsm
start with zsm = '4';
4)删除某一条数据
delete from zsm_2088 where zsm ='3'
5)connect by start with 查询数据
select '贴码级别[' || tmjb || '] ' || zsm as zsm,sqh,
'贴码级别[' || (to_number(tmjb) + 1) || '] ' || fjbzm as fjbzm
from zsm_2088
connect by fjbzm = prior zsm
start with zsm = '4';
6)查询报错信息
select '贴码级别[' || tmjb || '] ' || zsm as zsm,sqh,
'贴码级别[' || (to_number(tmjb) + 1) || '] ' || fjbzm as fjbzm
from zsm_2088
connect by fjbzm = prior zsm
start with zsm = '4' limit 0,7000
错误:[192.168.1.3:5050](GBA-02EX-0005) Failed to query in gnode:
DETAIL: Query failed.
CAUSEGBA-01EX-700) Gbase general error: Restrict: Connect by clause
must be used with table not deleted
SQL: SELECT /*192.168.1.4_21_345_2016-07-04_08:07:10*/ /*+ TID('2545')
*/ concat(concat(concat('贴码级别[', `syzs.zsm_2088`.`tmjb`), '] '),
`syzs.zsm_2088`.`zsm`) AS `zsm`, `syzs.zsm_2088`.`sqh` AS `sqh`,
concat(concat(concat('贴码级别[', (to_number(`syzs.zsm_2088`.`tmjb`) +
1)), '] '), `syzs.zsm_2088`.`fjbzm`)


1 回答

修改gnode 层的参数配置,添加:
_gbase_connect_by_support_table_with_deleted_records=1
删除记录的表上执行分级查询,可操作成功。

2018-9-13 14:01

撰写回答

您需要登录后才可以回帖 登录 | 立即注册

提问者

发布167
回答440

相关资料