DMP联调测试 - greatebee/AntDB GitHub Wiki
表结构调整
alter table qrtz_triggers alter column is_volatile type boolean USING is_volatile::boolean;
alter table qrtz_job_details alter column is_volatile type boolean USING is_volatile::boolean;
alter table qrtz_job_details alter column is_durable type boolean USING is_durable::boolean;
alter table qrtz_job_details alter column is_stateful type boolean USING is_stateful::boolean;
alter table qrtz_job_details alter column requests_recovery type boolean USING requests_recovery::boolean;
alter table qrtz_fired_triggers alter column is_volatile type boolean USING is_volatile::boolean;
alter table qrtz_fired_triggers alter column is_stateful type boolean USING is_stateful::boolean;
alter table qrtz_fired_triggers alter column requests_recovery type boolean USING requests_recovery::boolean;
alter table MAIL_OUTBOX alter column exclusive_flag type boolean USING exclusive_flag::boolean;
alter table SYS_ITF_HIST alter column is_success_flag type boolean USING is_success_flag::boolean;
alter table SYS_ITF_HIST alter column is_resp_party_flag type boolean USING is_resp_party_flag::boolean;
alter table BIZ_TEMPLATE_VER alter column not_affect_parent_biz_status type boolean USING not_affect_parent_biz_status::boolean;
系统函数
| 编号 |
函数名称 |
创建方式 |
| 1 |
oracle.sum(pg_catalog.numeric) |
手工 |
| 2 |
oracle.avg(pg_catalog.numeric) |
手工 |
| 3 |
oracle.max(pg_catalog.numeric) |
手工 |
| 4 |
oracle.max(pg_catalog.text) |
手工 |
| 5 |
oracle.nlssort |
自动 |
| 6 |
oracle.dmp_concat |
插件，create extension dmp_concat |
| 7 |
dmp_concat_distinct |
插件，create extension dmp_concat |
| 8 |
dmp_concat_dup |
插件，create extension dmp_concat |
| 9 |
rollup(record) |
自动 |
操作符
| 编号 |
操作符名称 |
创建方式 |
| 1 |
oracle.=(varchar,bytea) |
手工 |
| 2 |
oracle.!=(varchar,bytea) |
手工 |
| 3 |
oracle.>(varchar,bytea) |
手工 |
| 4 |
oracle.>=(varchar,bytea) |
手工 |
| 5 |
oracle.<(varchar,bytea) |
手工 |
| 6 |
oracle.<=(varchar,bytea) |
手工 |
| 7 |
oracle.=(varchar2,bytea) |
手工 |
| 8 |
oracle.!=(varchar2,bytea) |
手工 |
| 9 |
oracle.>(varchar2,bytea) |
手工 |
| 10 |
oracle.>=(varchar2,bytea) |
手工 |
| 11 |
oracle.<(varchar2,bytea) |
手工 |
| 12 |
oracle.<=(varchar2,bytea) |
手工 |
| 7 |
oracle.>(text,bytea) |
手工 |
| 8 |
oracle.>=(text,bytea) |
手工 |
| 9 |
oracle.<(text,bytea) |
手工 |
| 10 |
oracle.<=(text,bytea) |
手工 |
| 11 |
oracle.~~(text,bytea) |
手工 |
| 12 |
oracle.!~~(text,bytea) |
手工 |
临时表
| 编号 |
临时表名称 |
相关数据库对象 |
创建方式 |
| 1 |
temp_table_trans_01 |
ap_bulk_delete_biz |
手工 |
| 2 |
temp_table_trans_02 |
ap_bulk_delete_biz |
手工 |
| 3 |
analysis_temp1 |
ap_last_freezing_review |
手工 |
| 4 |
analysis_temp2 |
ap_last_freezing_review |
手工 |
| 5 |
analysis_temp3 |
ap_last_freezing_review |
手工 |
| 6 |
analysis_temp4 |
ap_last_freezing_review |
手工 |
ORACLE语法兼容
1. connect by
| 编号 |
语法 |
错误信息 |
说明 |
| 1 |
connect by + start with id in (subquery) |
column xxx does not exist |
|
| 2 |
connect by + distinct |
connect by not support distinct group window yet |
|
| 3 |
connect by + join |
connect by support one table yet |
|
| 4 |
connect by + with |
connect by only support RangeVar for now |
|
| 5 |
connect by + subquery |
connect by only support relation for now |
|
| 6 |
connect by + sys_connect_by_path |
ERROR: recursive query "reqmatrix" column 4 has type character varying(2000) in non-recursive term but type character varying overall |
|
| 7 |
connect by + group by |
ERROR: syntax error at or near "group" |
|
| 8 |
connect by + start with t1.id |
missing FROM-clause entry for table t2 |
|
| 9 |
connect by + order siblings by |
ERROR: syntax error at or near "siblings" |
|
2. others
| 编号 |
语法 |
错误信息 |
说明 |
| 1 |
nulls last/first |
ERROR: syntax error at or near "nulls" |
|
| 2 |
limit关键字 作为别名 |
ERROR: syntax error at or near limit $5 |
|
| 3 |
聚集函数 + order by |
ERROR: column "t.id" must appear in the GROUP BY clause or be used in an aggregate function |
select count(t.id) as col_0 from sy01 t order by t.id; |
| 4 |
数据类型隐式转换 |
operator does not exist: character varying = bytea |
创建对应的操作符 |
| 5 |
数据类型隐式转换 |
operator does not exist: character varying ~~ bytea |
创建对应的操作符 |
| 6 |
数据类型隐式转换 |
operator does not exist: character varying > bytea |
创建对应的操作符 |
| 7 |
临时字段大小写问题 |
列 I18N489 不存在 |
|
| 8 |
窗口函数 + distinct |
ERROR: cannot use DISTINCT with WITHIN GROUP |
select dmp_concat(distinct ped.role_id) over(partition by ped.emp_id) from pro_emp_date ped; |
| 9 |
CONTENT关键字 调整为非保留字 |
ERROR: syntax error at or near "CONTENT" |
|
| 10 |
兼容Oracle UID 关键字 |
ERROR: syntax error at or near "UID" |
|
| 11 |
聚合函数 + keep关键字 |
syntax error at or near ""dense_rank""" |
select min(id) keep(dense_rank first order by name asc nulls last) from sy01; |
| 12 |
subquery + rowid |
ERROR: column biz__.rowid does not exist |
|
| 13 |
select substr union select max |
ERROR: UNION types text and numeric cannot be matched |
|
| 14 |
聚合函数grouping(id) |
ERROR: ERROR: syntax error or near "grouping" |
select grouping(id) from sy01 group by rollup(id); |