[Bug] Insert into with sql error

[liujie-yt]

Search before asking

• I had searched in the issues and found no similar issues.

Version

2.1.4

What's Wrong?

SQL Error

insert into ads.ads_a_item_daily_stat_i_rt 
with tmp0 as (
			select a.*,
                   case when b.parent_sfc is null then b.SFC else b.parent_sfc end as parent_sfc_tmp,
                   b.parent_sfc                                                      as parent_sfc_tmp0
              from ads.ads_mes_ppm_nc_data_qty_stat_i_rt a
              left join dim.dim_mes_cn_sfc_t b on split_part(a.NC_CONTEXT_GBO, ',', 2) = b.SFC
              where substr(a.DATE_TIME, 1, 10) between DATE_SUB('2025-02-25', INTERVAL 20 DAY) and '2025-02-25'
), tmp2 as (select b.line_name,
                     a.nc_type,
                     substr(a.DATE_TIME, 1, 10) as CALC_DATE,
                     a.parent_sfc_tmp,
                     sum(a.qty)                 as nc_num
              from tmp0 a,
                   dim.dim_config_ppm_line_mapping_f_d b
              where a.resource = b.resource_id
                and a.operation = b.operation
                and split_part(NC_CONTEXT_GBO, ',', 2) not in
                    (select distinct parent_sfc_tmp0 from tmp0 where parent_sfc_tmp0 is not null)
              group by b.line_name, a.nc_type, a.parent_sfc_tmp, substr(a.DATE_TIME, 1, 10)
), tmp3 as (select t1.p_day, t1.resource, t1.operation, t1.item, t1.s * t2.ts as total_num
              from (select a.*,
                           split_part(split_part(b.BOM_BO, ':', 2), ',', 2) as BOM_INFO2,
                           split_part(split_part(b.BOM_BO, ':', 2), ',', 4) as BOM_INFO4
                    from (select a.p_day, a.resource, a.operation, a.shop_order, a.item, sum(a.qty) as s
                          from ads.ads_mes_ppm_activtiy_log_qty_stat_i_rt a
                          where p_day between DATE_SUB('2025-02-25', INTERVAL 20 DAY) and '2025-02-25'
                          group by a.p_day, a.resource, a.operation, a.shop_order, a.item) a
                             left join
                         dwd.dwd_mes_shop_order_detail_i_rt b
                         on a.shop_order = b.shop_order) t1,
                   (select b.BOM_INFO2, b.BOM_INFO4, b.OPERATION, sum(b.qty) as ts
                    from ads.ads_mes_ppm_bom_oeration_qty_stat_i_rt b
                    group by b.BOM_INFO2, b.BOM_INFO4, b.OPERATION) t2
              where t1.BOM_INFO2 = t2.BOM_INFO2
                and t1.BOM_INFO4 = t2.BOM_INFO4
                and t1.OPERATION = t2.OPERATION
)
select 
       n.line_name,
       n.item,
       n.p_day,
       m.nc_num ,
       n.total_num ,
       now() as ETL_INSERT_TIME ,
       now() as ETL_UPDATE_TIME 
from (
         select t3.p_day,
                t4.line_name,
                t3.item,
                sum(t3.total_num) as total_num
         from tmp3 t3,
              dim.dim_config_ppm_line_mapping_f_d t4
         where t3.resource = t4.resource_id
           and t3.operation = t4.operation
         group by t3.p_day,
                  t4.line_name,
                  t3.item
     ) n left join (
         select a.line_name, b.ITEM as ITEM, a.CALC_DATE, sum(a.nc_num) nc_num
         from tmp2 a left join (
                  		select split_part(b.NC_CONTEXT_GBO, ',', 2) sfc, item
                             from ads.ads_mes_ppm_nc_data_qty_stat_i_rt
                             group by split_part(b.NC_CONTEXT_GBO, ',', 2), item
                  ) b on a.parent_sfc_tmp = b.sfc
         group by a.line_name, b.ITEM, a.CALC_DATE
     ) m
     on n.line_name = m.line_name
         and n.item = m.item
         and n.p_day = m.CALC_DATE

SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 2:
with tmp0 as (

     ^
Encountered: IDENTIFIER
Expected

org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1105] [HY000]: errCode = 2, detailMessage = Syntax error in line 2:
with tmp0 as (

     ^
Encountered: IDENTIFIER
Expected


	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:615)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:506)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:528)

	at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:977)

	at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4155)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)

	at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:194)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)

	at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5147)

	at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:117)

	at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Caused by: java.sql.SQLException: errCode = 2, detailMessage = Syntax error in line 2:
with tmp0 as (

     ^
Encountered: IDENTIFIER
Expected


	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:130)

	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)

	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:767)

	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:652)

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)

	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)

	... 11 more

What You Expected?

execution sucess

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct