公用表表达式（CTE）不支持 INSERT INTO TBL_NAME

Viewed 34
2.1

Doris 版本

Doris 版本: 2.1.3

公用表表达式（CTE）不支持 INSERT INTO TBL_NAME

不支持
INSERT INTO tbl_name
WITH
cte1 AS (SELECT a，b FROM table1)，
cte2 AS (SELECT c，d FROM table2)
SELECT b，d
FROM cte1
JOIN cte2
WHERE cte1.a = cte2.c;

详情

详见: https://github.com/apache/doris/issues/41491

示例

WITH 语法异常

有以下SQL语句

ALTER TABLE dwd.dwd_inv_proj_corp_d DROP PARTITION IF EXISTS p20240928;
ALTER TABLE dwd.dwd_inv_proj_corp_d ADD PARTITION IF NOT EXISTS p20240928 VALUES IN ('2024-09-28');

INSERT INTO dwd.dwd_inv_proj_corp_d PARTITION (p20240928)
WITH t1 AS (
SELECT *
FROM ods.ods_cfhec_pro_inv_com_inf_f_d PARTITION (p20240928)
WHERE del_flag = '0'
)
,t2 AS (
SELECT pro_com_id
,sum(case when gd_category = '01' then (cgbl / 100) else 0 end) AS sum_cg
FROM ods.ods_cfhec_pro_inv_com_inf_gd_f_d PARTITION (p20240928)
GROUP BY pro_com_id
)
SELECT NULL AS proj_md_code
,t1.inv_pro_id AS proj_code --项目编码
,t1.id AS proj_corp_code --项目公司编码
,t1.pro_com_name AS proj_corp_name --项目公司名称(唯一性)
,round(t1.zczj, 2) AS reg_cap --注册资金
,t1.register_area AS reg_location --注册地
,t1.fddbr AS lpr --法定代表人
,t1.register_date AS reg_date --注册时间
,t1.com_zjl AS corp_gm --公司总经理
,t1.zjl_tel AS gm_tel --总经理联系方式
,t1.com_cwfzr AS corp_fin_prin --公司财务负责人
,t1.cw_tel AS fin_tel --财务联系方式
,round(t2.sum_cg / 100, 4) AS cfhec_group_shr_right --一公局集团所占股权(%)
,round(t1.zgjjszgq / 100, 4) AS cccc_consortium_shr_right --中交内联合体所占股权(%)
,t1.yyzz AS busi_license --营业执照
,t1.yyzzh AS busi_license_num --营业执照号
,t1.gszc AS articles_of_corp --公司章程
,t1.wfgddbmc AS our_side_shrhd_rept_name --我方股东代表名称
,t1.create_date AS create_date
,t1.update_date AS update_date
,t1.del_flag AS del_flag --删除标记
,current_timestamp() AS etl_time --etl加载时间
,'' AS source_system --来源系统
,'ods_cfhec_pro_inv_com_inf_f_d,ods_cfhec_pro_inv_com_inf_gd_f_d' AS source_table --来源表名
,'2024-09-28' AS etl_part --分区字段
FROM t1
LEFT JOIN t2
ON t1.id = t2.pro_com_id
;

报错信息

当执行 INSERT INTO WITH SELECT 的时候, 居然报错
SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 5:
WITH t1 AS (
^
Encountered: IDENTIFIER
Expected

____1
2 Answers

相关的表结构麻烦提供一下，在提问内容中更新即可

黄海军@SelectDB2461

DWD 建表语句

DWD 建表语句 dwd_inv_proj_corp_d

CREATE TABLE dwd_inv_proj_corp_d ( proj_md_code VARCHAR(96) NULL COMMENT '项目主数据编码', proj_code VARCHAR(96) NULL COMMENT '项目编码', proj_corp_code VARCHAR(96) NULL COMMENT '项目公司编码', proj_corp_name VARCHAR(2000) NULL COMMENT '项目公司名称(唯一性)', reg_cap DECIMAL(18, 2) NULL COMMENT '注册资金', reg_location VARCHAR(2000) NULL COMMENT '注册地', lpr VARCHAR(2000) NULL COMMENT '法定代表人', reg_date DATETIME NULL COMMENT '注册时间', corp_gm VARCHAR(2000) NULL COMMENT '公司总经理', gm_tel VARCHAR(2000) NULL COMMENT '总经理联系方式', corp_fin_prin VARCHAR(2000) NULL COMMENT '公司财务负责人', fin_tel VARCHAR(2000) NULL COMMENT '财务联系方式', cfhec_group_shr_right DECIMAL(18, 4) NULL COMMENT '一公局集团所占股权(%)', cccc_consortium_shr_right DECIMAL(18, 4) NULL COMMENT '中交内联合体所占股权(%)', busi_license VARCHAR(2000) NULL COMMENT '营业执照', busi_license_num VARCHAR(2000) NULL COMMENT '营业执照号', articles_of_corp TEXT NULL COMMENT '公司章程', our_side_shrhd_rept_name VARCHAR(2000) NULL COMMENT '我方股东代表名称', create_date DATETIME NULL COMMENT '创建时间', update_date DATETIME NULL COMMENT '更新时间', del_flag VARCHAR(2000) NULL COMMENT '删除标记', etl_time DATETIME NULL COMMENT 'ETL加载时间', source_system VARCHAR(2000) NULL COMMENT '来源系统', source_table VARCHAR(2000) NULL COMMENT '来源表名', etl_part VARCHAR(100) NULL COMMENT '分区字段' ) ENGINE=OLAP COMMENT '投资项目公司基本信息' PARTITION BY LIST(etl_part) (PARTITION p20240928 VALUES IN ("2024-09-28"), PARTITION p20240929 VALUES IN ("2024-09-29"), PARTITION p20241001 VALUES IN ("2024-10-01"), PARTITION p20241003 VALUES IN ("2024-10-03"), PARTITION p20241007 VALUES IN ("2024-10-07"), PARTITION p20241008 VALUES IN ("2024-10-08"), PARTITION p20241009 VALUES IN ("2024-10-09"), PARTITION p20241010 VALUES IN ("2024-10-10"), PARTITION p20241011 VALUES IN ("2024-10-11"), PARTITION p20241012 VALUES IN ("2024-10-12"), PARTITION p20241013 VALUES IN ("2024-10-13"), PARTITION p20241014 VALUES IN ("2024-10-14"), PARTITION p20241015 VALUES IN ("2024-10-15")) DISTRIBUTED BY HASH(proj_code) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V1", "estimate_partition_size" = "2G", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_duplicate_without_keys_by_default" = "true" );

ODS 建表语句

ODS 建表语句 ods_cfhec_pro_inv_com_inf_f_d

CREATE TABLE ods_cfhec_pro_inv_com_inf_f_d ( id VARCHAR(96) NULL COMMENT '主键id', pro_com_name VARCHAR(300) NULL COMMENT '项目公司名称(唯一性)', zczj DECIMAL(18, 2) NULL COMMENT '注册资金', register_area VARCHAR(96) NULL COMMENT '注册地', fddbr VARCHAR(300) NULL COMMENT '法定代表人', register_date DATETIME NULL COMMENT '注册时间', com_zjl VARCHAR(300) NULL COMMENT '公司总经理', zjl_tel VARCHAR(150) NULL COMMENT '总经理联系方式', com_cwfzr VARCHAR(300) NULL COMMENT '公司财务负责人', cw_tel VARCHAR(150) NULL COMMENT '财务联系方式', szgq DECIMAL(18, 6) NULL COMMENT '所占股权', gldw VARCHAR(300) NULL COMMENT '管理单位', yyzz VARCHAR(300) NULL COMMENT '营业执照', gszc TEXT NULL COMMENT '公司章程', create_by VARCHAR(96) NULL COMMENT '创建者', create_org VARCHAR(96) NULL COMMENT '创建单位', create_date DATETIME NULL COMMENT '创建时间', update_by VARCHAR(96) NULL COMMENT '更新者', update_org VARCHAR(96) NULL COMMENT '更新单位', update_date DATETIME NULL COMMENT '更新时间', remarks VARCHAR(900) NULL COMMENT '备注信息', del_flag VARCHAR(3) NULL COMMENT '删除标记', inv_pro_id VARCHAR(96) NULL COMMENT '投资项目id', xmmc VARCHAR(96) NULL COMMENT '项目名称', wfgddbmc VARCHAR(300) NULL COMMENT '我方股东代表名称', zgjjszgq DECIMAL(18, 6) NULL COMMENT '中国交建所占股权', yyzzh VARCHAR(1500) NULL COMMENT '营业执照号', etl_part VARCHAR(100) NULL COMMENT '分区字段' ) ENGINE=OLAP COMMENT '投资项目公司基本信息' PARTITION BY LIST(etl_part) (PARTITION p20240926 VALUES IN ("2024-09-26"), PARTITION p20240928 VALUES IN ("2024-09-28"), PARTITION p20240929 VALUES IN ("2024-09-29"), PARTITION p20241001 VALUES IN ("2024-10-01"), PARTITION p20241003 VALUES IN ("2024-10-03"), PARTITION p20241007 VALUES IN ("2024-10-07"), PARTITION p20241008 VALUES IN ("2024-10-08"), PARTITION p20241009 VALUES IN ("2024-10-09"), PARTITION p20241010 VALUES IN ("2024-10-10"), PARTITION p20241011 VALUES IN ("2024-10-11"), PARTITION p20241012 VALUES IN ("2024-10-12"), PARTITION p20241013 VALUES IN ("2024-10-13"), PARTITION p20241014 VALUES IN ("2024-10-14"), PARTITION p20241015 VALUES IN ("2024-10-15")) DISTRIBUTED BY HASH(id) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V1", "estimate_partition_size" = "2G", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_duplicate_without_keys_by_default" = "true" );

ODS 建表语句 ods_cfhec_pro_inv_com_inf_gd_f_d

CREATE TABLE ods_cfhec_pro_inv_com_inf_gd_f_d ( gd_category VARCHAR(300) NULL COMMENT '股东类型', pro_com_id VARCHAR(96) NULL COMMENT '项目公司信息id', gd_name VARCHAR(300) NULL COMMENT '股东名称', cgbl DECIMAL(18, 6) NULL COMMENT '持股比例', fpbl DECIMAL(18, 6) NULL COMMENT '分配比例', cze DECIMAL(18, 2) NULL COMMENT '出资额', inv_pro_id VARCHAR(96) NULL COMMENT '投资项目id', gd_id VARCHAR(96) NULL COMMENT '股东ID（内部股东）', id VARCHAR(96) NULL, etl_part VARCHAR(100) NULL COMMENT '分区字段' ) ENGINE=OLAP COMMENT '投资项目公司股东信息' PARTITION BY LIST(etl_part) (PARTITION p20240926 VALUES IN ("2024-09-26"), PARTITION p20240928 VALUES IN ("2024-09-28"), PARTITION p20240929 VALUES IN ("2024-09-29"), PARTITION p20241001 VALUES IN ("2024-10-01"), PARTITION p20241003 VALUES IN ("2024-10-03"), PARTITION p20241007 VALUES IN ("2024-10-07"), PARTITION p20241008 VALUES IN ("2024-10-08"), PARTITION p20241009 VALUES IN ("2024-10-09"), PARTITION p20241010 VALUES IN ("2024-10-10"), PARTITION p20241011 VALUES IN ("2024-10-11"), PARTITION p20241012 VALUES IN ("2024-10-12"), PARTITION p20241013 VALUES IN ("2024-10-13"), PARTITION p20241014 VALUES IN ("2024-10-14"), PARTITION p20241015 VALUES IN ("2024-10-15")) DISTRIBUTED BY HASH(id) BUCKETS AUTO PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "min_load_replica_num" = "-1", "is_being_synced" = "false", "storage_medium" = "hdd", "storage_format" = "V2", "inverted_index_storage_format" = "V1", "estimate_partition_size" = "2G", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false", "group_commit_interval_ms" = "10000", "group_commit_data_bytes" = "134217728", "enable_duplicate_without_keys_by_default" = "true" );

____1