针对与 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.