Doris版本:2.1.4
只运行select可以出结果,创建视图会报错。
创建物化视图sql:
-- 创建物化视图
create materialized view testdb.mes_PLDetailQuery_v
BUILD IMMEDIATE -- 立即刷新
REFRESH ON SCHEDULE EVERY 1 HOUR STARTS "2024-07-29 11:00:00" -- 自动定时刷新
DISTRIBUTED BY RANDOM BUCKETS 3
as
select tak.order_id_s AS gongdan,
plf.module_id_s_s AS tiaoma,
nc.Batch_Code_Small_s AS pici,
plf.product_type_s AS xinghao,
nc.zh_marks_desc_s AS leixing,
plf.pmax_f AS gonglv,
plf.power_grade_i AS mingpai,
iv.voc_f AS voc,
iv.isc_f AS isc,
iv.vpm_f AS vpmax,
iv.ipm_f AS ipmax,
iv.ff_f AS ff,
iv.tempdut_f AS tdut,
plf.iv_equipment_s AS shebei,
plf.creation_time AS riqi,
nvl(plf.cell_ratios_f, 0) AS xiaolv,
b.sale_order_desc_s AS kehu,
case
when vc.srmcode_s is null then
vc.vendorname_s
else
concat_ws(sdt.value_s , vc.srmcode_s)
end AS cell,
case
when vg.srmcode_s is null then
vg.vendorname_s
else
concat_ws(sdt.value_s , vg.srmcode_s)
end AS glass,
det.box_id_s,
a.area_name
from testdb.at_tmmodulesplinfor plf, testdb.at_sysdictionary sd, testdb.at_sysdictionarydata sdt
inner join testdb.at_pmmoduletask tak
on tak.Module_Id_s = plf.module_id_s_s
INNER JOIN testdb.at_pmordertask ord
ON ord.order_id_s = tak.order_id_s
INNER JOIN testdb.area a
ON ord.area_s = a.area_name
inner join (select mp.module_id_s,
mp.material_code_s,
mim.zh_marks_desc_s,
mp.Batch_Code_Small_s
from testdb.at_immaterialsmarks mim
left join testdb.at_pmmodulepasswcconsume mp
on mim.part_number_s = mp.material_code_s
where material_code_s like '71%') nc
on nc.module_id_s = plf.module_id_s_s
inner join testdb.at_pmivtestrecord iv
on iv.atr_key = plf.iv_key_i
LEFT OUTER JOIN testdb.at_erpvendorinfo vc
ON vc.vendorcode_s = plf.cell_manufacturer_s
LEFT OUTER JOIN testdb.at_erpvendorinfo vg
ON vg.vendorcode_s = plf.glass_manufacturer_s
LEFT OUTER JOIN testdb.at_pmpackagedetail det
ON det.module_id_s = plf.module_id_s_s
left join testdb.at_sales_order b
on ord.customer_order_id_s = b.sale_order_no_s
where sd.atr_key = sdt.parent_key
and sd.name_s = 'MaterialFirstStr';
报错:
org.jkiss.dbeaver.model.sql.DBSQLException: SQL 错误 [1105] [HY000]: Unexpected exception: errCode = 2, detailMessage = Unknown column 'module_id_s_s' in 'plf' in JOIN clause
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:582)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$1(SQLQueryJob.java:491)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:498)
at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:924)
at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3805)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:123)
at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:173)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:121)
at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5073)
at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: Unexpected exception: errCode = 2, detailMessage = Unknown column 'module_id_s_s' in 'plf' in JOIN clause
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:96)
at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
... 12 more