create or replace view vw_ys_ysmbyd_mzdzbl
(ypnm00, ykbmbh, ypbm00, ypgbm0, yzyyp0, pysm00, wbsm00, ypmc00, ypgg00, ypsysm, ypjcl0, kcdw00, jldw00, zyfydw, mzfydw, k2j000, z2j000, m2j000, pfj000, lsj000, zbj000, gjj000, gjpjdj, gjzje0, gjzsl0, cyjl00, kcsx00, kcxx00, kcsl00, kcje00, pdxh00, sfkyyy, sfzbyp, sfpsyy, qzfs00, cfjb00, gzdj00, mzdj00, dpdj00, sjdj00, cfwz00, lbbh00, ylbh00, jxbh00, qyrq00, tzrq00, dxsybz, kcgjje, yfid00, pcid00, cyl000, zqs000, zjbz00, tzbz00, ydypmc, ypmc02, ypmclb, f2j000, ypczfl, hxmc00, mzkfdw)
as
select y.ypnm00,y.ykbmbh,y.ypbm00,y.ypgbm0,y.yzyyp0,y.pysm00,y.wbsm00,y.ypmc00,y.ypgg00,y.ypsysm,
       y.ypjcl0,trim(y.kcdw00)kcdw00,trim(y.jldw00) JLDW00,trim(y.zyfydw)zyfydw,trim(y.mzfydw)mzfydw,
       y.k2j000,y.z2j000,y.m2j000,y.pfj000,y.lsj000,
       y.zbj000,y.gjj000,y.gjpjdj,y.gjzje0,y.gjzsl0,y.cyjl00,y.kcsx00,y.kcxx00,y.kcsl00,y.kcje00,
       y.pdxh00,y.sfkyyy,y.sfzbyp,y.sfpsyy,y.qzfs00,y.cfjb00,y.gzdj00,y.mzdj00,y.dpdj00,y.sjdj00,
       y.cfwz00,y.lbbh00,y.ylbh00,y.jxbh00,y.qyrq00,y.tzrq00,y.dxsybz,y.kcgjje,y.yfid00,
       y.pcid00,round(y.CYL000/y.F2J000,4)*y.NSBL00,y.zqs000,y.zjbz00, sf_ys_sfyfls(y.ypnm00) TZBZ00 ,y.ypmc00 ydypmc,
             y.yptym0 ypmc02,--药品名称2
             y.ypmclb,    --ypmc00字段代表的名称 0: 通用名 1：商品名 2： 别名
             y.f2j000,y.ypczfl,hxmc00,y.mzkfdw
from bm_yd0000 y  where pdxh00<>'0' and y.tzrq00 is null

union all

select y.ypnm00,y.ykbmbh,y.ypbm00,y.ypgbm0,y.yzyyp0,z.pysm00,z.wbsm00,y.ypmc00,y.ypgg00,y.ypsysm,
       y.ypjcl0,trim(y.kcdw00)kcdw00,trim(y.jldw00) JLDW00,trim(y.zyfydw)zyfydw,trim(y.mzfydw)mzfydw,
       y.k2j000,y.z2j000,y.m2j000,y.pfj000,y.lsj000,
       y.zbj000,y.gjj000,y.gjpjdj,y.gjzje0,y.gjzsl0,y.cyjl00,y.kcsx00,y.kcxx00,y.kcsl00,y.kcje00,
       y.pdxh00,y.sfkyyy,y.sfzbyp,y.sfpsyy,y.qzfs00,y.cfjb00,y.gzdj00,y.mzdj00,y.dpdj00,y.sjdj00,
       y.cfwz00,y.lbbh00,y.ylbh00,y.jxbh00,y.qyrq00,y.tzrq00,y.dxsybz,y.kcgjje,y.yfid00,
       y.pcid00,round(y.CYL000/y.F2J000,4)*y.NSBL00,y.zqs000,y.zjbz00, sf_ys_sfyfls(y.ypnm00) TZBZ00, z.bmmc00,
             DECODE(z.ypmclb,'0',y.ypmc00,'1',y.ypmc00,'2',decode(y.ypmclb,'0',y.yptym0,'1',y.ypmc00)) ypmc02,--药品名称2
             z.ypmclb,          --ypmc00字段代表的名称 0: 通用名 1：商品名 2： 别名
             y.f2j000,y.ypczfl,hxmc00,y.mzkfdw
from bm_yd0000 y, bm_ypbm00 z  where pdxh00<>'0' and y.ypnm00=z.ypnm00 and y.tzrq00 is null
;