YAS-00014 illegal conversion from VARCHAR to -

-- 表结构：

drop TABLE SYS_DOC_DIRECTORY;

CREATE TABLE SYS_DOC_DIRECTORY (

        DIRECTORY_OID VARCHAR(128) NOT NULL,

        PARENT_DIRECTORY_OID VARCHAR(128),

        DIRECTORY_CODE VARCHAR(200),

        DIRECTORY_NAME VARCHAR(800),

        ORDER_SEQ INTEGER,

        DIRECTORY_DESC VARCHAR(800),

        REMARK VARCHAR(4000),

        CREATED_BY_CODE VARCHAR(80),

        CREATED_BY_NAME VARCHAR(400),

        CREATED_DATE VARCHAR(400),

        UPDATED_BY_CODE VARCHAR(80),

        UPDATED_BY_NAME VARCHAR(400),

        UPDATED_DATE VARCHAR(400),

        SYSTEM_CODE VARCHAR(40),

        CONSTRAINT CONS134241653 PRIMARY KEY (DIRECTORY_OID)

);

-- 表数据：

INSERT INTO SYS_DOC_DIRECTORY

(DIRECTORY_OID, PARENT_DIRECTORY_OID, DIRECTORY_CODE, DIRECTORY_NAME, ORDER_SEQ, DIRECTORY_DESC, REMARK, CREATED_BY_CODE, CREATED_BY_NAME, CREATED_DATE, UPDATED_BY_CODE, UPDATED_BY_NAME, UPDATED_DATE, SYSTEM_CODE)

VALUES('BB6470A80A024522901E06D04CE23A95', NULL, 'helpCenter', '帮助文档', 1, NULL, NULL, 'admin', '脚本插入', '2021-11-23 11:09:28.000', 'admin', '超级管理员', '2021-11-23 11:09:28.000', '9999');

INSERT INTO SYS_DOC_DIRECTORY

(DIRECTORY_OID, PARENT_DIRECTORY_OID, DIRECTORY_CODE, DIRECTORY_NAME, ORDER_SEQ, DIRECTORY_DESC, REMARK, CREATED_BY_CODE, CREATED_BY_NAME, CREATED_DATE, UPDATED_BY_CODE, UPDATED_BY_NAME, UPDATED_DATE, SYSTEM_CODE)

VALUES('B542698011781D71F9046A9D8F913789', 'BB6470A80A024522901E06D04CE23A95', 'helpCenter', '脚本插入', 3, NULL, NULL, 'admin', '超级管理员', '2023-01-31 11:10:02.000', 'admin', '超级管理员', '2023-01-31 16:29:59.000', '9999');

-- 报错sql：

WITH docDirectory (directory_oid, parent_directory_oid, directory_code, directory_name,

dis, directory_desc, remark, created_by_code, created_by_name, created_date, updated_by_code, updated_by_name, updated_date)

AS (SELECT * FROM (SELECT directory_oid, parent_directory_oid, directory_code, directory_name, order_seq dis, directory_desc,

remark, created_by_code, created_by_name, created_date, updated_by_code, updated_by_name, updated_date

FROM SYS_DOC_DIRECTORY

WHERE parent_directory_oid IS NULL AND SYSTEM_CODE = '9999'

UNION ALL

SELECT c.directory_oid, c.parent_directory_oid, c.directory_code, c.directory_name, c.order_seq dis, c.directory_desc, c.remark,

c.created_by_code, c.created_by_name, c.created_date, c.updated_by_code, c.updated_by_name, c.updated_date

FROM SYS_DOC_DIRECTORY c INNER JOIN SYS_DOC_DIRECTORY p ON p.directory_oid = c.parent_directory_oid) ORDER BY dis DESC),

docDirectoryDis (directory_oid, parent_directory_oid, directory_code, directory_name, dis, directory_desc, remark, created_by_code, created_by_name,

created_date, updated_by_code, updated_by_name, updated_date, order_seq, parent_directory_name)

AS (

SELECT d.directory_oid, d.parent_directory_oid, d.directory_code, d.directory_name, d.dis, d.directory_desc, d.remark,

d.created_by_code, d.created_by_name, d.created_date, d.updated_by_code, d.updated_by_name, d.updated_date, to_char(d.dis) order_seq,

NULL parent_directory_name FROM docDirectory d WHERE d.parent_directory_oid IS NULL

UNION ALL

SELECT t.directory_oid, t.parent_directory_oid, t.directory_code,

t.directory_name, t.dis, t.directory_desc, t.remark, t.created_by_code, t.created_by_name, t.created_date, t.updated_by_code, t.updated_by_name, t.updated_date,

concat(concat(td.order_seq, '_'), t.dis) order_seq,

(SELECT directory_name FROM SYS_DOC_DIRECTORY WHERE directory_oid = t.parent_directory_oid) parent_directory_name

FROM docDirectory t

INNER JOIN docDirectoryDis td ON t.parent_directory_oid = td.directory_oid)

SELECT COUNT(*) AS total FROM docDirectoryDis dis ;

-- 改写规避

WITH docDirectory (directory_oid, parent_directory_oid, directory_code, directory_name,

dis, directory_desc, remark, created_by_code, created_by_name, created_date, updated_by_code, updated_by_name, updated_date)

AS (SELECT * FROM (SELECT directory_oid, parent_directory_oid, directory_code, directory_name, order_seq dis, directory_desc,

remark, created_by_code, created_by_name, created_date, updated_by_code, updated_by_name, updated_date

FROM SYS_DOC_DIRECTORY

WHERE parent_directory_oid IS NULL AND SYSTEM_CODE = '9999'

UNION ALL

SELECT c.directory_oid, c.parent_directory_oid, c.directory_code, c.directory_name, c.order_seq dis, c.directory_desc, c.remark,

c.created_by_code, c.created_by_name, c.created_date, c.updated_by_code, c.updated_by_name, c.updated_date

FROM SYS_DOC_DIRECTORY c INNER JOIN SYS_DOC_DIRECTORY p ON p.directory_oid = c.parent_directory_oid) ORDER BY dis DESC),

docDirectoryDis (directory_oid, parent_directory_oid, directory_code, directory_name, dis, directory_desc, remark, created_by_code, created_by_name,

created_date, updated_by_code, updated_by_name, updated_date, order_seq, parent_directory_name)

AS (

SELECT d.directory_oid, d.parent_directory_oid, d.directory_code, d.directory_name, d.dis, d.directory_desc, d.remark,

d.created_by_code, d.created_by_name, d.created_date, d.updated_by_code, d.updated_by_name, d.updated_date, to_char(d.dis) order_seq,

'-' parent_directory_name FROM docDirectory d WHERE d.parent_directory_oid IS NULL

UNION ALL

SELECT t.directory_oid, t.parent_directory_oid, t.directory_code,

t.directory_name, t.dis, t.directory_desc, t.remark, t.created_by_code, t.created_by_name, t.created_date, t.updated_by_code, t.updated_by_name, t.updated_date,

concat(concat(td.order_seq, '_'), t.dis) order_seq,

(SELECT directory_name FROM SYS_DOC_DIRECTORY WHERE directory_oid = t.parent_directory_oid) parent_directory_name

FROM docDirectory t

INNER JOIN docDirectoryDis td ON t.parent_directory_oid = td.directory_oid)

SELECT COUNT(*) AS total FROM docDirectoryDis dis ;