AI 助理
文档备案控制台
开发者社区 数据库 文章 正文

KDTS迁移视图报错ERROR: syntax error at or near "IF"

2024-11-22 451
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDSClaw，2核4GB
RDS DuckDB + QuickBI 企业套餐，8核32GB + QuickBI 专业版
PolarClaw，2核4GB
简介： KDTS迁移视图报错ERROR: syntax error at or near "IF"

ERROR: syntax error at or near "IF"

问题描述

错误报告中显示如下报错：

PORTAL.V_DATAQUALITY_ORGAN fail:
com.kingbase.kdts.h.b: sql=IF EXISTS(select * from sys.views v left join sys_namespace n on n.oid = v.schema_id and nspname = 'public' where NAME='V_DATAQUALITY_ORGAN') DROP VIEW  "public"."V_DATAQUALITY_ORGAN" 
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:327)
  at com.kingbase.kdts.dboperator.a.execute(AbstractDbOperator.java:293)
  at com.kingbase.kdts.job.migration.at.bf(ViewWriteJob.java:57)
  at com.kingbase.kdts.job.migration.at.call(ViewWriteJob.java:25)
  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
  at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: com.kingbase8.util.KSQLException: ERROR: syntax error at or near "IF"
  Position: 1 At Line: 1, Line Position: 1
  at com.kingbase8.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3675)
  at com.kingbase8.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:3101)
  at com.kingbase8.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  at com.kingbase8.jdbc.KbStatement.executeInternal_(KbStatement.java:806)
  at com.kingbase8.jdbc.KbStatement.execute(KbStatement.java:698)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:601)
  at com.kingbase8.jdbc.KbStatement.executeCachedSql(KbStatement.java:575)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:536)
  at com.kingbase8.jdbc.KbStatement.executeUpdate(KbStatement.java:478)
  at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119)
  at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:317)
  ... 7 common frames omitted
PORTAL.VIEW_SYS_MENU_ORGAN fail:
com.kingbase.kdts.h.b: sql=IF EXISTS(select * from sys.views v left join sys_namespace n on n.oid = v.schema_id and nspname = 'public' where NAME='VIEW_SYS_MENU_ORGAN') DROP VIEW  "public"."VIEW_SYS_MENU_ORGAN" 
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:327)
  at com.kingbase.kdts.dboperator.a.execute(AbstractDbOperator.java:293)
  at com.kingbase.kdts.job.migration.at.bf(ViewWriteJob.java:57)
  at com.kingbase.kdts.job.migration.at.call(ViewWriteJob.java:25)
  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
  at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: com.kingbase8.util.KSQLException: ERROR: syntax error at or near "IF"
  Position: 1 At Line: 1, Line Position: 1
  at com.kingbase8.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3675)
  at com.kingbase8.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:3101)
  at com.kingbase8.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  at com.kingbase8.jdbc.KbStatement.executeInternal_(KbStatement.java:806)
  at com.kingbase8.jdbc.KbStatement.execute(KbStatement.java:698)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:601)
  at com.kingbase8.jdbc.KbStatement.executeCachedSql(KbStatement.java:575)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:536)
  at com.kingbase8.jdbc.KbStatement.executeUpdate(KbStatement.java:478)
  at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119)
  at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:317)
  ... 7 common frames omitted
PORTAL.V_SZ_DM_JG fail:
com.kingbase.kdts.h.b: sql=IF EXISTS(select * from sys.views v left join sys_namespace n on n.oid = v.schema_id and nspname = 'public' where NAME='V_SZ_DM_JG') DROP VIEW  "public"."V_SZ_DM_JG" 
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:327)
  at com.kingbase.kdts.dboperator.a.execute(AbstractDbOperator.java:293)
  at com.kingbase.kdts.job.migration.at.bf(ViewWriteJob.java:57)
  at com.kingbase.kdts.job.migration.at.call(ViewWriteJob.java:25)
  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
  at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: com.kingbase8.util.KSQLException: ERROR: syntax error at or near "IF"
  Position: 1 At Line: 1, Line Position: 1
  at com.kingbase8.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3675)
  at com.kingbase8.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:3101)
  at com.kingbase8.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  at com.kingbase8.jdbc.KbStatement.executeInternal_(KbStatement.java:806)
  at com.kingbase8.jdbc.KbStatement.execute(KbStatement.java:698)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:601)
  at com.kingbase8.jdbc.KbStatement.executeCachedSql(KbStatement.java:575)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:536)
  at com.kingbase8.jdbc.KbStatement.executeUpdate(KbStatement.java:478)
  at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119)
  at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:317)
  ... 7 common frames omitted
PORTAL.V_DATAQUALITY_USER fail:
com.kingbase.kdts.h.b: sql=IF EXISTS(select * from sys.views v left join sys_namespace n on n.oid = v.schema_id and nspname = 'public' where NAME='V_DATAQUALITY_USER') DROP VIEW  "public"."V_DATAQUALITY_USER" 
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:327)
  at com.kingbase.kdts.dboperator.a.execute(AbstractDbOperator.java:293)
  at com.kingbase.kdts.job.migration.at.bf(ViewWriteJob.java:57)
  at com.kingbase.kdts.job.migration.at.call(ViewWriteJob.java:25)
  at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
  at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: com.kingbase8.util.KSQLException: ERROR: syntax error at or near "IF"
  Position: 1 At Line: 1, Line Position: 1
  at com.kingbase8.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:3675)
  at com.kingbase8.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:3101)
  at com.kingbase8.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
  at com.kingbase8.jdbc.KbStatement.executeInternal_(KbStatement.java:806)
  at com.kingbase8.jdbc.KbStatement.execute(KbStatement.java:698)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:601)
  at com.kingbase8.jdbc.KbStatement.executeCachedSql(KbStatement.java:575)
  at com.kingbase8.jdbc.KbStatement.executeWithFlags(KbStatement.java:536)
  at com.kingbase8.jdbc.KbStatement.executeUpdate(KbStatement.java:478)
  at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:119)
  at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
  at com.kingbase.kdts.dboperator.a.aQ(AbstractDbOperator.java:317)
  ... 7 common frames omitted

选中某个视图提示如下报错

-- ERROR: syntax error at or near "IF"
  Position: 1 At Line: 1, Line Position: 1
而KDTS详情中源库Oracle中视图sql显示为空

解决办法

Oracle源库中生成视图sql，如下：

CREATE VIEW PORTAL.V_DATAQUALITY_ORGAN ( ID, VALID, NAME, CODE, GRADE, PARENT, SHORTNAME, ORGANTYPECODE, PRINTTEXT )
AS
SELECT ID --主键ID
, TO_NUMBER(STATE,'999') AS VALID --状态：01：正常；02：注销 ; 03: 删除; 04 不可用
, NAME --名称
, CODE --代码
, GRADE --级次
, PARENT_ID AS PARENT --父机构ID
, NULLIF(SHORT_NAME, CODE) AS SHORTNAME --简称
, ORGAN_TYPE_CODE AS ORGANTYPECODE --机构类型 
, PRINT_TEXT AS PRINTTEXT --局轨
FROM AAF_ORGAN 
GO

将PORTAL.去掉，直接在目标库KES中创建


作者：董小姐yyds

Oracle 11g OCM，KCP,PGCE，墨天轮技术专家，青学会成员

擅长主流Oracle、MySQL、PG安装部署、故障定位处理、性能优化，

金仓、达梦、openGauss系等各种商用和开源国产数据库

微信:margaretdy

公众号：董小姐yyds

视频号：DongxiaojieYYDS

墨天轮：https://www.modb.pro/u/3121

CSDN：https://blog.csdn.net/qq961573863?type=blog

知乎：https://www.zhihu.com/people/fuwaxiaohuan

文章标签：
关系型数据库
Oracle
SQL
运维
MySQL
游客guv2kz4qqvpn2
目录
相关文章
仍在探索未知中
|
缓存 Ubuntu 前端开发
Linux配置yum源以及基本yum指令
Linux配置yum源以及基本yum指令
仍在探索未知中
4348 1
张思睿ZGQ
|
存储 关系型数据库 MySQL
【分布式和微服务1】一篇文章详细了解分布式和微服务的基本概念
【分布式和微服务1】一篇文章详细了解分布式和微服务的基本概念
张思睿ZGQ
2035 0
一只牛博
|
5月前
|
人工智能 缓存 语音技术
基于Rokid CXR-M SDK实现AR智能助手应用：让AI大模型走进AR眼镜
本文记录使用Rokid CXR-M SDK开发AR AI助手的全过程，涵盖SDK集成、语音识别、AI对接、结果推送等核心功能，分享实际开发中的技术选型、架构设计与踩坑经验，实现解放双手的实时语音问答体验。
一只牛博
751 6
基于Rokid CXR-M SDK实现AR智能助手应用：让AI大模型走进AR眼镜
javaercn
|
5月前
|
存储 安全 数据安全/隐私保护
HashiCorp Vault 镜像拉取与 Docker 部署全指南
Vault是HashiCorp推出的企业级秘密管理工具，支持集中存储、细粒度访问控制、动态凭证生成与全链路审计。通过Docker部署可实现环境一致、安全隔离、快速迭代，适用于开发测试及生产环境，助力企业高效安全管理敏感信息。
javaercn
418 3
蚝油菜花
|
敏捷开发 人工智能 JavaScript
Figma-Low-Code：快速将Figma设计转换为Vue.js应用，支持低代码渲染、数据绑定
Figma-Low-Code 是一个开源项目，能够直接将 Figma 设计转换为 Vue.js 应用程序，减少设计师与开发者之间的交接时间，支持低代码渲染和数据绑定。
蚝油菜花
1465 3
Figma-Low-Code：快速将Figma设计转换为Vue.js应用，支持低代码渲染、数据绑定
蓝易云
|
8月前
|
SQL XML Java
MyBatis框架如何处理字符串相等的判断条件。
总的来说，MyBatis框架提供了灵活而强大的机制来处理SQL语句中的字符串相等判断条件。无论是简单的等值判断，还是复杂的条件逻辑，MyBatis都能通过其标签和属性来实现，使得动态SQL的编写既安全又高效。
蓝易云
684 0
潜意识Java
|
前端开发 Java 开发工具
Git使用教程-将idea本地Java等文件配置到gitte上【保姆级教程】
本内容详细介绍了使用Git进行版本控制的全过程，涵盖从本地仓库创建到远程仓库配置，以及最终推送代码至远程仓库的步骤。
潜意识Java
1784 0
流楚丶格念
|
Ubuntu 关系型数据库 MySQL
libmysqlclient.so.18（libmysqlclient.so.20 libmysqlclien.so） =＞ not found
libmysqlclient.so.18（libmysqlclient.so.20 libmysqlclien.so） =＞ not found
流楚丶格念
1702 0
libmysqlclient.so.18（libmysqlclient.so.20 libmysqlclien.so） =＞ not found
是Yu欸
|
关系型数据库 MySQL 数据库连接
解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝
是Yu欸
8198 52
解决 mysql8.0 ERROR 1045 (28000): Access denied for user ‘ODBC‘@‘localhost‘ (using password: NO)用户访问拒绝