创建备份表是行转列报错，去除PIVOT行转列又可以创建成功，这是什么原因？
CREATE TABLE TMP_C_CUST_INFO AS
SELECT * FROM (
SELECT B.CORE_CUST_NO,A.CL_NAME,
(CASE WHEN A.OPT_TYPE=‘40’ THEN
CASE WHEN B.CORE_CUST_NO IS NOT NULL THEN ‘1’ ELSE ‘0’ END
WHEN A.OPT_TYPE IN (‘20’,‘30’) THEN
CASE WHEN B.CORE_CUST_NO IS NOT NULL THEN B.TAG_NAME ELSE NULL END
WHEN A.OPT_TYPE=‘10’ THEN
CASE WHEN B.CORE_CUST_NO IS NOT NULL THEN B.TAG_NO ELSE NULL END
END) CL_VALUE
FROM QUERY_COLUMN A
inner join CUST_REJECT B
ON A.GROUP_NO = B.GROUP_NO
WHERE A.IS_SYCN=‘1’
AND A.BUSI_LINE=‘2’
AND A.IS_DEL=‘0’
)
PIVOT (
MAX(CL_VALUE)
FOR CL_NAME IN (‘cl001’)
)
ORDER BY CORE_CUST_NO
失败原因：
ErrorCode = 900, SQLState = 42000, Details = You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near ‘cl001’’,3,16384,6,3), (0,573983,1,’__co_default’,0,16384,6,1), (0,573983,1004,’_’ at line 1
OB版本4.3.2，
QUERY_COLUMN表结构：
CREATE TABLE QUERY_COLUMN (
“GROUP_NO” VARCHAR2(64) NOT NULL ENABLE,
“GROUP_NAME” VARCHAR2(256),
“CL_NAME” VARCHAR2(50),
“CL_C_NAME” VARCHAR2(50),
“CL_TYPE” VARCHAR2(50),
“CL_LENGTH” VARCHAR2(50),
“IS_SYCN” VARCHAR2(50) DEFAULT 0,
“OPT_TYPE” VARCHAR2(50),
“BUSI_LINE” VARCHAR2(5),
“IS_DEL” VARCHAR2(5),
“CREATE_DATE” DATE,
“DEL_DATE” DATE
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column);
COMMENT ON TABLE QUERY_COLUMN IS ‘灵活查询标签字段记录’;
COMMENT ON COLUMN QUERY_COLUMN.“GROUP_NO” IS ‘分组编号’;
COMMENT ON COLUMN QUERY_COLUMN.“GROUP_NAME” IS ‘分组名称’;
COMMENT ON COLUMN QUERY_COLUMN.“CL_NAME” IS ‘字段名称’;
COMMENT ON COLUMN QUERY_COLUMN.“CL_C_NAME” IS ‘字段中文名称’;
COMMENT ON COLUMN QUERY_COLUMN.“CL_TYPE” IS ‘字段类型’;
COMMENT ON COLUMN QUERY_COLUMN.“CL_LENGTH” IS ‘字段长度’;
COMMENT ON COLUMN QUERY_COLUMN.“IS_SYCN” IS ‘是否同步灵活查询:0-否 1-是’;
COMMENT ON COLUMN QUERY_COLUMN.“OPT_TYPE” IS ‘运算符类型：10-下拉框 20-数值 30-文本 40-是否’;
COMMENT ON COLUMN QUERY_COLUMN.“BUSI_LINE” IS ‘业务条线(1-个人，2-公司)’;
COMMENT ON COLUMN QUERY_COLUMN.“IS_DEL” IS ‘是否已删除’;
COMMENT ON COLUMN QUERY_COLUMN.“CREATE_DATE” IS ‘新增时间’;
COMMENT ON COLUMN QUERY_COLUMN.“DEL_DATE” IS ‘删除时间’;
CUST_REJECT表结构：
CREATE TABLE CUST_REJECT (
“TAG_NO” VARCHAR2(64) NOT NULL ENABLE,
“CORE_CUST_NO” VARCHAR2(64),
“CREDIT_CUST_NO” VARCHAR2(64),
“CUST_TYPE” VARCHAR2(64),
“LAST_CHG_USR” VARCHAR2(32),
“LAST_CHG_DT” VARCHAR2(20),
“TAG_NAME” VARCHAR2(256),
“GROUP_NAME” VARCHAR2(256),
“TAG_DESC” VARCHAR2(2000),
“PROCESS_MODE” VARCHAR2(64),
“GROUP_NO” VARCHAR2(64),
“CREATE_SYS” VARCHAR2(64),
“START_DATE” VARCHAR2(8),
“DUE_DATE” VARCHAR2(8)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 WITH COLUMN GROUP(each column);
COMMENT ON TABLE CUST_REJECT IS ‘对公客户标签关联表’;
COMMENT ON COLUMN CUST_REJECT.“TAG_NO” IS ‘标签编号’;
COMMENT ON COLUMN CUST_REJECT.“CORE_CUST_NO” IS ‘核心客户号’;
COMMENT ON COLUMN CUST_REJECT.“CREDIT_CUST_NO” IS ‘信贷客户号’;
COMMENT ON COLUMN CUST_REJECT.“CUST_TYPE” IS ‘客户类型 1-个人 2-对公’;
COMMENT ON COLUMN CUST_REJECT.“LAST_CHG_USR” IS ‘最新变更用户’;
COMMENT ON COLUMN CUST_REJECT.“LAST_CHG_DT” IS ‘最新变更时间’;
COMMENT ON COLUMN CUST_REJECT.“TAG_NAME” IS ‘标签名称’;
COMMENT ON COLUMN CUST_REJECT.“GROUP_NAME” IS ‘标签分组名称’;
COMMENT ON COLUMN CUST_REJECT.“TAG_DESC” IS ‘标签描述’;
COMMENT ON COLUMN CUST_REJECT.“PROCESS_MODE” IS ‘加工方式manual:手工,statistics:统计,mining:挖掘’;
COMMENT ON COLUMN CUST_REJECT.“GROUP_NO” IS ‘标签分组’;
COMMENT ON COLUMN CUST_REJECT.“CREATE_SYS” IS ‘来源系统 CRM-人工录入 STATIST-系统统计’;
COMMENT ON COLUMN CUST_REJECT.“START_DATE” IS ‘开始日期’;
COMMENT ON COLUMN CUST_REJECT.“DUE_DATE” IS ‘到期日期’;
CREATE INDEX “NEWCRM”.“INX_COMP_CORE_CUST_NO” on CUST_REJECT (
“CORE_CUST_NO”
) GLOBAL ;
CREATE INDEX “NEWCRM”.“INX_COMP_TAG_NO” on CUST_REJECT (
“TAG_NO”
) GLOBAL ;
