Doris版本：2.1.4
只运行select可以出结果，创建视图会报错。
建表sql
-- 创建目标表
drop table testdb.at_sysdictionary;
create table testdb.at_sysdictionary
(
atr_key BIGINT,
site_num INT,
atr_name STRING,
purge_status INT,
creation_time DATETIME,
creation_time_u DATETIME,
creation_time_z STRING,
last_modified_time DATETIME,
last_modified_time_u DATETIME,
last_modified_time_z STRING,
xfr_insert_pid INT,
xfr_update_pid INT,
trx_id STRING,
parent_key BIGINT,
category_s STRING,
description_s STRING,
name_s STRING,
readonly_y INT
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_sysdictionarydata;
create table testdb.at_sysdictionarydata
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
description_s string,
key_s string,
sort_i bigint,
value_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_tmmodulesplinfor;
create table testdb.at_tmmodulesplinfor
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
ivtest_time_t datetime,
ivtest_time_u datetime,
ivtest_time_z string,
iv_equipment_s string,
iv_key_i bigint,
pmax_f float,
cell_area_f float,
cell_manufacturer_s string,
cell_partnum_s string,
cell_piece_f float,
cell_ratios_f float,
cell_technology_s string,
current_grade_s string,
eva_manufacturer_s string,
eva_partnum_s string,
eva_spec_s string,
glass_manufacturer_s string,
glass_partnum_s string,
glass_spec_s string,
hited_y int,
is_calculate_y int,
j_box_length_s string,
j_box_manufacturer_s string,
j_box_partnum_s string,
join_manufacturer_s string,
join_partnum_s string,
module_id_s_s string,
pl_calculate_f float,
power_grade_i bigint,
power_level5_i bigint,
power_level6_i bigint,
product_type_s string,
sale_no_s string,
strip_manufacturer_s string,
strip_partnum_s string,
tergum_manufacturer_s string,
tergum_partnum_s string,
granding_key_i bigint,
poe_manufacture_s string,
poe_partnum_s string,
poe_spec_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_pmmoduletask;
create table testdb.at_pmmoduletask
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
column_1_s string,
complement_person_s string,
create_by_s string,
founday_remark_s string,
i_level_s string,
i_value_f float,
is_check_s string,
is_lock_s string,
is_return_to_line_s string,
is_scrap_s string,
is_ng_s string,
is_pack_s string,
last_update_by_s string,
lock_id_s string,
module_id_s string,
module_level_s string,
order_id_old_s string,
order_id_s string,
power_level_s string,
power_value_f float,
print_no_i bigint,
print_time_t datetime,
print_time_u datetime,
print_time_z string,
print_qty_i bigint,
printer_s string,
product_code_s string,
production_line_s string,
reissue_date_t datetime,
reissue_date_u datetime,
reissue_date_z string,
reissue_person_s string,
rework_order_id_s string,
shift_task_id_s string,
start_online_time_t datetime,
start_online_time_u datetime,
start_online_time_z string,
status_s string,
work_center_s string,
switch_rule_id_s string,
mark_print_time_t datetime,
mark_print_time_u datetime,
mark_print_time_z string,
mark_power_level_s string,
mark_current_level_s string,
iv_key_i bigint,
is_instorage_s string,
isrelease_s string,
releaseby_s string,
release_time_t datetime,
release_time_u datetime,
release_time_z string,
recycle_time_t datetime,
recycle_time_u datetime,
recycle_time_z string,
area_s string,
qrcode_id_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_pmordertask;
create table testdb.at_pmordertask
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
actual_end_time_t datetime,
actual_end_time_u datetime,
actual_end_time_z string,
actual_start_time_t datetime,
actual_start_time_u datetime,
actual_start_time_z string,
area_s string,
attr1_s string,
attr2_s string,
attr3_s string,
attr4_s string,
attr5_s string,
bom_revision_s string,
cell_color_s string,
cell_power_s string,
complete_qty_f float,
craft_confirm_time_t datetime,
craft_confirm_time_u datetime,
craft_confirm_time_z string,
craft_confirmer_s string,
created_by_s string,
customer_order_id_s string,
dispatched_qty_f float,
last_update_by_s string,
module_print_qty_f float,
module_print_status_i bigint,
oem_i bigint,
order_id_s string,
order_name_s string,
order_status_s string,
order_type_s string,
order_type_desc_s string,
org_code_s string,
original_order_id_s string,
package_qty_i bigint,
plan_end_time_t datetime,
plan_end_time_u datetime,
plan_end_time_z string,
plan_start_time_t datetime,
plan_start_time_u datetime,
plan_start_time_z string,
power_max_s string,
power_min_s string,
product_code_s string,
product_name_s string,
production_line_s string,
quantity_f float,
released_qty_f float,
remain_qty_f float,
route_s string,
route_rev_s string,
sale_no_s string,
schedule_group_name_s string,
scrap_qty_f float,
single_box_no_s string,
status_i bigint,
switch_rule_id_s string,
target_power_s string,
third_party_monitor_s string,
uom_s string,
wip_qty_f float,
inst_id_s string,
islocked_s string,
ci_code_s string,
rfid_order_no_s string,
is_check_s string,
poe_s string,
glass_type_s string,
specifiedmaterial_s string,
confirmby_s string,
close_time_t datetime,
close_time_u datetime,
close_time_z string,
hit_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.area;
create table testdb.area
(
area_key bigint,
site_num int,
area_name string,
description string,
category string,
creator_key bigint,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modifier_key bigint,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
update_privilege_key bigint,
delete_privilege_key bigint,
location_key bigint,
inst_list_key bigint,
work_schedule_key bigint,
time_zone string
)unique key(area_key)
distributed by hash(area_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_immaterialsmarks;
create table testdb.at_immaterialsmarks
(
atr_key BIGINT,
site_num INT,
atr_name STRING,
purge_status INT,
creation_time DATETIME,
creation_time_u DATETIME,
creation_time_z STRING,
last_modified_time DATETIME,
last_modified_time_u DATETIME,
last_modified_time_z STRING,
xfr_insert_pid INT,
xfr_update_pid INT,
trx_id CHAR(38),
parent_key BIGINT,
part_number_s STRING,
create_by_s STRING,
en_marks_desc_s STRING,
modify_by_s STRING,
reserves_s STRING,
zh_marks_desc_s STRING
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_pmmodulepasswcconsume;
create table testdb.at_pmmodulepasswcconsume
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
area_s string,
batch_code_big_s string,
batch_code_small_s string,
cousume_time_t datetime,
cousume_time_u datetime,
cousume_time_z string,
created_by_s string,
last_update_by_s string,
mat_consume_no_f float,
material_code_s string,
material_group_s string,
module_id_s string,
module_pass_id_s string,
operation_code_s string,
order_id_s string,
parent_material_code_s string,
product_code_s string,
production_line_s string,
remark_s string,
shift_s string,
shift_task_id_s string,
status_s string,
work_center_code_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_pmivtestrecord;
create table testdb.at_pmivtestrecord
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id char(38),
parent_key bigint,
area_s string,
cirr_f float,
column_1_i bigint,
created_by_s string,
eff_f float,
equipment_id_s string,
ff_f float,
gavg_f float,
ipm_f float,
irr_f float,
isc_f float,
lti_f float,
last_update_by_s string,
module_id_s string,
order_id_s string,
pmax_f float,
production_line_s string,
rs_f float,
rsh_f float,
shift_task_id_s string,
status_s string,
tmod_f float,
tempdut_f float,
tempmc_f float,
test_time_t datetime,
test_time_u datetime,
test_time_z string,
voc_f float,
vpm_f float,
sequence_id_s string,
sequence_num_i bigint
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_erpvendorinfo;
create table testdb.at_erpvendorinfo
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
vendoraddress_s string,
vendoraltname_s string,
vendorclass_s string,
vendorcode_s string,
vendorname_s string,
srmcode_s string,
supplierinitials_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
drop table testdb.at_pmpackagedetail;
create table testdb.at_pmpackagedetail
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
actual_current_f float,
actual_power_f float,
box_id_s string,
check_result_s string,
create_by_s string,
current_grade_s string,
gread_record_key_s string,
group_id_s string,
is_sample_s string,
last_update_by_s string,
module_id_s string,
module_sequence_i bigint,
order_id_s string,
package_time_t datetime,
package_time_u datetime,
package_time_z string,
part_number_s string,
power_grade_s string,
product_grade_s string,
production_line_s string,
sample_result_s string,
status_s string
)unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
create table testdb.at_sales_order
(
atr_key bigint,
site_num int,
atr_name string,
purge_status int,
creation_time datetime,
creation_time_u datetime,
creation_time_z string,
last_modified_time datetime,
last_modified_time_u datetime,
last_modified_time_z string,
xfr_insert_pid int,
xfr_update_pid int,
trx_id string,
parent_key bigint,
barcode_starting_s string,
barcode_template_s string,
battery_rail_s string,
battery_type_s string,
border_template_s string,
created_by_s string,
executive_standard_s string,
last_update_by_s string,
mark_template_s string,
module_type_s string,
nameplate_template_s string,
packing_number_i bigint,
packing_size_s string,
production_type_s string,
remarks_s string,
reserve_s string,
sale_order_desc_s string,
sale_order_no_s string,
sop_document_s string,
status_s string,
unit_weight_f float,
sales_type_s string,
custom_rule_module_s string,
custom_rule_box_s string,
ci_code_s string,
rfid_order_no_s string,
nameplate_type_s string,
nameplate_ver_s string,
devanning_report_s string,
ischeck_s string,
sh_by_s string,
sh_time_s string,
additional_temple_s string,
wirelen_s string,
predicted_yield_s string,
predicted_pct_s string,
shipmark_remark_s string,
pl_remark_s string,
hide_status_s string,
power_pct_s string,
calculatepl_s string,
is_prodution_s string
)
unique key(atr_key, site_num)
distributed by hash(atr_key) buckets 3
properties
(
"replication_allocation" = "tag.location.default: 3",
"enable_unique_key_merge_on_write"="true"
);
创建物化视图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
