Oracle 建立視圖（View）執行個體

標籤：person   art   lin   div   span   and   code   nbsp   oracle   

文法：

create or replace 【ViewName】 asselect * from QueryTable[s]

 

執行個體：

create or replace view vm_wo asselect ‘2‘ as sources,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_time  from wo_mstr  join pt_mstr on pt_part = wo_part  join wop1_mstr on pt_size = wop_ptsize  left join wopd1_det on wopd1_nbr = wop_nbr and pt_cmt = wopd1_capacity_type  left join wopd2_det on wopd2_nbr = wop_nbr and pt_group = wopd2_capacity_type  left join (      select pzd_wo_nbr,pzd_wo_lot, nvl(sum(pzd_people_count*pzd_rel_hour),0) wo_rel_hour      from pzd_det a  join pz_mstr b on pz_nbr = pzd_nbr      group by pzd_wo_nbr,pzd_wo_lot  ) t on t.pzd_wo_nbr = wo_nbr and t.pzd_wo_lot = wo_lot  where 1=1  and trunc(sysdate,‘dd‘) between wop_start and wop_end  union all  select ‘1‘ as sources,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_time  from (select 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,wo_std_pl,wo_man_haur,wo_qty_comp from huinan.wo_mstr@huinanerp)  join huinan.pt_mstr@huinanerp on pt_part = wo_part  join huinan.wop1_mstr@huinanerp on pt_size = wop_ptsize  left join huinan.wopd1_det@huinanerp on wopd1_nbr = wop_nbr and pt_cmt = wopd1_capacity_type  left join huinan.wopd2_det@huinanerp on wopd2_nbr = wop_nbr and pt_group = wopd2_capacity_type  left join (      select pzd_wo_nbr,pzd_wo_lot, nvl(sum(pzd_people_count*pzd_rel_hour),0) wo_rel_hour      from huinan.pzd_det@huinanerp a  join huinan.pz_mstr@huinanerp b on pz_nbr = pzd_nbr      group by pzd_wo_nbr,pzd_wo_lot  ) t on t.pzd_wo_nbr = wo_nbr and t.pzd_wo_lot = wo_lot  where 1=1  and trunc(sysdate,‘dd‘) between wop_start and wop_end;

 

Oracle 建立視圖（View）執行個體