meri oracle view_Oracle 创建视图（View）实例

create or replace view vm_wo as

select ‘2’ assources,wo_nbr,wo_lot,wo_cust,wo_part,wo_rel_date,wo_due_date,wo_qty_ord,wo_status,wo_line,wo_so_nbr,wo_so_line,

wo_soft,wo_soft_ver,wo_rmks,wo_mod_date,

(case when wop_ptsize like ‘%LED%TV%’ then nvl(wopd1_capacity_ratio,0)when wop_ptsize like ‘%LED%PANEL%’ then nvl(wopd2_capacity_ratio,0)else 0 end) wo_standard_ratio,round(decode(nvl(t.wo_rel_hour,0),0,0,(case when wop_ptsize like ‘%LED%TV%’ then nvl(wopd1_capacity_ratio,0)when wop_ptsize like ‘%LED%PANEL%’ then nvl(wopd2_capacity_ratio,0)else 0 end) * wo_qty_comp / t.wo_rel_hour),4) wo_standard_efficient,

nvl(wo_std_pl,0) wo_standard_person,round(nvl(wo_man_haur,0),4) wo_standard_timefromwo_mstrjoin pt_mstr on pt_part =wo_partjoin wop1_mstr on pt_size =wop_ptsizeleft join wopd1_det on wopd1_nbr = wop_nbr and pt_cmt =wopd1_capacity_typeleft join wopd2_det on wopd2_nbr = wop_nbr and pt_group =wopd2_capacity_typeleft join(select pzd_wo_nbr,pzd_wo_lot, nvl(sum(pzd_people_count*pzd_rel_hour),0) wo_rel_hourfrom pzd_det a join pz_mstr b on pz_nbr =pzd_nbrgroup bypzd_wo_nbr,pzd_wo_lot

) ton t.pzd_wo_nbr = wo_nbr and t.pzd_wo_lot =wo_lotwhere 1=1

and trunc(sysdate,’dd’) between wop_start andwop_endunion all

select ‘1’ assources,wo_nbr,wo_lot,wo_cust,wo_part,wo_rel_date,wo_due_date,wo_qty_ord,wo_status,wo_line,wo_so_nbr,wo_so_line,

wo_soft,wo_soft_ver,wo_rmks,wo_mod_date,

(case when wop_ptsize like ‘%LED%TV%’ then nvl(wopd1_capacity_ratio,0)when wop_ptsize like ‘%LED%PANEL%’ then nvl(wopd2_capacity_ratio,0)else 0 end) wo_standard_ratio,round(decode(nvl(t.wo_rel_hour,0),0,0,(case when wop_ptsize like ‘%LED%TV%’ then nvl(wopd1_capacity_ratio,0)when wop_ptsize like ‘%LED%PANEL%’ then nvl(wopd2_capacity_ratio,0)else 0 end) * wo_qty_comp / t.wo_rel_hour),4) wo_standard_efficient,

nvl(wo_std_pl,0) wo_standard_person,round(nvl(wo_man_haur,0),4) wo_standard_timefrom (selectwo_nbr,wo_lot,wo_cust,wo_part,wo_rel_date,wo_due_date,wo_qty_ord,wo_status,wo_line,wo_so_nbr,wo_so_line,

wo_soft,wo_soft_ver,wo_rmks,wo_mod_date,wo_std_pl,wo_man_haur,wo_qty_compfrom huinan.wo_mstr@huinanerp)join huinan.pt_mstr@huinanerp on pt_part =wo_partjoin huinan.wop1_mstr@huinanerp on pt_size =wop_ptsizeleft join huinan.wopd1_det@huinanerp on wopd1_nbr = wop_nbr and pt_cmt =wopd1_capacity_typeleft join huinan.wopd2_det@huinanerp on wopd2_nbr = wop_nbr and pt_group =wopd2_capacity_typeleft join(select pzd_wo_nbr,pzd_wo_lot, nvl(sum(pzd_people_count*pzd_rel_hour),0) wo_rel_hourfrom huinan.pzd_det@huinanerp a join huinan.pz_mstr@huinanerp b on pz_nbr =pzd_nbrgroup bypzd_wo_nbr,pzd_wo_lot

) ton t.pzd_wo_nbr = wo_nbr and t.pzd_wo_lot =wo_lotwhere 1=1

and trunc(sysdate,’dd’) between wop_start and wop_end;