在做缓慢变化的过程中，在用minus对比维表和ODS表时，有106条数据，insert到临时表显示插入0条，查询临时表也未查到数据。
# 插入临时表语句
insert into bi_ods.bidim_tpm_equipment_d_temp
(
department ,
created_by ,
created_time ,
delete_flag ,
updated_by ,
updated_time ,
asset_code ,
axis_number ,
brand ,
charge_person ,
equip_code ,
equip_code_mdc,
cost_center ,
department_code ,
expired_date ,
fixed_value ,
group_code ,
inspect_code ,
local ,
manufacturer_id ,
model ,
name ,
organization_id ,
original_value ,
parent_id ,
percision_value ,
power ,
priority ,
production_date ,
program_code ,
purchase_date ,
serial_number ,
ship_date ,
status ,
supplier_id ,
system_model ,
table_size ,
tool_number ,
travel ,
type ,
useful_life ,
voltage ,
located_in_id ,
system_model_new ,
capacity
)
select
'FTHAP' as department ,
a.created_by ,
a.created_time ,
a.delete_flag,
a.updated_by ,
a.updated_time ,
a.asset_code ,
a.axis_number,
a.brand,
a.charge_person,
a.code as equip_code ,
b.equipment_id as equip_code_mdc,
a.cost_center,
a.department_code,
a.expired_date ,
a.fixed_value,
a.group_code ,
a.inspect_code ,
a.local,
a.manufacturer_id,
a.model,
a.name ,
a.organization_id,
a.original_value ,
a.parent_id,
a.percision_value,
a.power,
a.priority ,
a.production_date,
a.program_code ,
a.purchase_date,
a.serial_number,
a.ship_date,
a.status ,
a.supplier_id,
a.system_model ,
a.table_size ,
a.tool_number,
a.travel ,
a.type ,
a.useful_life,
a.voltage,
a.located_in_id,
a.system_model_new ,
a.capacity
from bi_ods.f_tpm_equipment_fthap a
left join bi_ods.f_eam_equipment_master_cs b on a.asset_code=b.asset_number
minus
select
department ,
created_by ,
created_time ,
delete_flag ,
updated_by ,
updated_time ,
asset_code ,
axis_number ,
brand ,
charge_person ,
equip_code ,
equip_code_mdc,
cost_center ,
department_code ,
expired_date ,
fixed_value ,
group_code ,
inspect_code ,
local ,
manufacturer_id ,
model ,
name ,
organization_id ,
original_value ,
parent_id ,
percision_value ,
power ,
priority ,
production_date ,
program_code ,
purchase_date ,
serial_number ,
ship_date ,
status ,
supplier_id ,
system_model ,
table_size ,
tool_number ,
travel ,
type ,
useful_life ,
voltage ,
located_in_id ,
system_model_new ,
capacity
from bi_dim.bidim_tpm_equipment_d
执行结果
去掉insert后查询如下
在minus外面套一层select语句
select * from (
select
'FTHAP' as department ,
a.created_by ,
a.created_time ,
a.delete_flag,
a.updated_by ,
a.updated_time ,
a.asset_code ,
a.axis_number,
a.brand,
a.charge_person,
a.code as equip_code ,
b.equipment_id as equip_code_mdc,
a.cost_center,
a.department_code,
a.expired_date ,
a.fixed_value,
a.group_code ,
a.inspect_code ,
a.local,
a.manufacturer_id,
a.model,
a.name ,
a.organization_id,
a.original_value ,
a.parent_id,
a.percision_value,
a.power,
a.priority ,
a.production_date,
a.program_code ,
a.purchase_date,
a.serial_number,
a.ship_date,
a.status ,
a.supplier_id,
a.system_model ,
a.table_size ,
a.tool_number,
a.travel ,
a.type ,
a.useful_life,
a.voltage,
a.located_in_id,
a.system_model_new ,
a.capacity
from bi_ods.f_tpm_equipment_fthap a
left join bi_ods.f_eam_equipment_master_cs b on a.asset_code=b.asset_number
minus
select
department ,
created_by ,
created_time ,
delete_flag ,
updated_by ,
updated_time ,
asset_code ,
axis_number ,
brand ,
charge_person ,
equip_code ,
equip_code_mdc,
cost_center ,
department_code ,
expired_date ,
fixed_value ,
group_code ,
inspect_code ,
local ,
manufacturer_id ,
model ,
name ,
organization_id ,
original_value ,
parent_id ,
percision_value ,
power ,
priority ,
production_date ,
program_code ,
purchase_date ,
serial_number ,
ship_date ,
status ,
supplier_id ,
system_model ,
table_size ,
tool_number ,
travel ,
type ,
useful_life ,
voltage ,
located_in_id ,
system_model_new ,
capacity
from bi_dim.bidim_tpm_equipment_d
) s
结果如下：
