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
