针对与 INSERT INTO tbl_name PARTITION (pxxx) WITH .... SELECT 报错

[xqg1316]

ALTER TABLE tbl_name DROP PARTITION IF EXISTS ${etl_part_key}; ALTER TABLE tbl_name ADD PARTITION IF NOT EXISTS ${etl_part_key} VALUES IN ('${etl_part}');

INSERT INTO tbl_name PARTITION (p20240928) WITH t1 AS ( SELECT * FROM tbl_1 PARTITION (p20240928) WHERE del_flag = '0' ) SELECT NULL AS proj_md_code ,id AS proj_code ,zjpftze AS resc_invest_amt ,pfsj AS resc_time ,pfwh AS resc_docu_num ,jsnx AS const_month ,hgnx AS buyback_year ,yynx AS op_year ,kshgyyrq AS op_start_date ,jsrq AS op_end_date ,kshgyyrq AS start_buyback_date ,jsrq AS end_buyback_date ,zbrq AS outbid_date ,NULL AS work_rpt ,NULL AS work_calc_tbl ,NULL AS invest_meeting_ask ,NULL AS invest_calc_tbl ,NULL AS invest_rpt ,NULL AS meeting_minutes ,bz AS remark ,create_date AS create_date ,update_date AS update_date ,del_flag AS del_flag ,current_timestamp() AS etl_time ,'' AS source_system ,'tbl_1' AS source_table ,'2024-09-28' AS etl_part FROM t1 ;

1. 这样的写法报错

单独的 WITH .... SELECT 可以正常执行; 如果在前面加上 INSERT INTO 操作, 在接 WITH SELECT直接报错

报错内容: Execution failed: Error Failed to execute sql: org.apache.doris.common.AnalysisException: errCode = 2, detailMessage = Syntax error in line 1: ...DROP PARTITION IF EXISTS ${etl_part_key}; ^ Encountered: { Expected: ||, COMMA, ., IDENTIFIER

[KassieZ]

Thank you for the feedback, will update this issue to engineer and double check.

[KassieZ]

关于故障排除、报错问题可以前往 Doris 中文论坛 提问，有专门技术人员答疑与支持

[morningman]

If want to use CTE in insert into, your statement has to be like:

insert into tbl1 with label your_label with t1 as(xxx) xxx;

That is, there must be a with label your_label in the statement.

[morningman]

or if you are using version above 2.1, which is parsing with antler, this is not a problem, which means you don't need with label at all in new version.