视图代码如下:
CREATE VIEW dbo.v_0200_rs
AS
SELECT TOP 100 PERCENT dbo.en_0001_prnmsr.ennmcd, dbo.en_0001_prnmsr.ennm,
dbo.en_0002_dscdnm.dsnm, dbo.en_0002_dscdnm.dscd, dbo.en_0206_rspp.ttst,
dbo.en_0001_prnmsr.entp, dbo.en_0210_dam.dmtp, dbo.en_0210_dam.mxdmhg,
dbo.en_0210_dam.dmtpel, dbo.en_0202_rshypr.drbsar
FROM dbo.en_0002_dscdnm INNER JOIN
dbo.en_0006_dsen ON
dbo.en_0002_dscdnm.dscd = dbo.en_0006_dsen.dscd RIGHT OUTER JOIN
dbo.en_0001_prnmsr ON
dbo.en_0006_dsen.ennmcd = dbo.en_0001_prnmsr.ennmcd LEFT OUTER JOIN
dbo.en_0202_rshypr ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0202_rshypr.ennmcd LEFT OUTER JOIN
dbo.en_0210_dam ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0210_dam.ennmcd LEFT OUTER JOIN
dbo.en_0206_rspp ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0206_rspp.ennmcd
WHERE (dbo.en_0001_prnmsr.entp = 'B')
ORDER BY dbo.en_0002_dscdnm.dscd小弟没有用过ORACLE,这个视图里面一堆的左链接,把我搞晕了,不知道如何处理,请高手帮我讲讲,顺便帮我轮换成ORACLE的,小弟在此先谢谢了!!!!!!!!!!!!!!
CREATE VIEW dbo.v_0200_rs
AS
SELECT TOP 100 PERCENT dbo.en_0001_prnmsr.ennmcd, dbo.en_0001_prnmsr.ennm,
dbo.en_0002_dscdnm.dsnm, dbo.en_0002_dscdnm.dscd, dbo.en_0206_rspp.ttst,
dbo.en_0001_prnmsr.entp, dbo.en_0210_dam.dmtp, dbo.en_0210_dam.mxdmhg,
dbo.en_0210_dam.dmtpel, dbo.en_0202_rshypr.drbsar
FROM dbo.en_0002_dscdnm INNER JOIN
dbo.en_0006_dsen ON
dbo.en_0002_dscdnm.dscd = dbo.en_0006_dsen.dscd RIGHT OUTER JOIN
dbo.en_0001_prnmsr ON
dbo.en_0006_dsen.ennmcd = dbo.en_0001_prnmsr.ennmcd LEFT OUTER JOIN
dbo.en_0202_rshypr ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0202_rshypr.ennmcd LEFT OUTER JOIN
dbo.en_0210_dam ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0210_dam.ennmcd LEFT OUTER JOIN
dbo.en_0206_rspp ON
dbo.en_0001_prnmsr.ennmcd = dbo.en_0206_rspp.ennmcd
WHERE (dbo.en_0001_prnmsr.entp = 'B')
ORDER BY dbo.en_0002_dscdnm.dscd小弟没有用过ORACLE,这个视图里面一堆的左链接,把我搞晕了,不知道如何处理,请高手帮我讲讲,顺便帮我轮换成ORACLE的,小弟在此先谢谢了!!!!!!!!!!!!!!
TOP用ROW_NUMBER来做吧,左右连接就用(+)代替.
AS
(SELECT row_number()over(ORDER BY en_0002_dscdnm.dscd) as rn,en_0001_prnmsr.ennmcd, en_0001_prnmsr.ennm,
en_0002_dscdnm.dsnm, en_0002_dscdnm.dscd, en_0206_rspp.ttst,
en_0001_prnmsr.entp, en_0210_dam.dmtp, en_0210_dam.mxdmhg,
en_0210_dam.dmtpel, en_0202_rshypr.drbsar
FROM en_0001_prnmsr,en_0002_dscdnm,en_0006_dsen,en_0001_prnmsr,en_0202_rshypr,en_0210_dam,en_0206_rspp
WHERE en_0001_prnmsr.entp = 'B'
and en_0002_dscdnm.dscd = en_0006_dsen.dscd
and en_0006_dsen.ennmcd(+) = en_0001_prnmsr.ennmcd
and en_0001_prnmsr.ennmcd = en_0202_rshypr.ennmcd(+)
and en_0001_prnmsr.ennmcd = en_0210_dam.ennmcd(+)
and en_0001_prnmsr.ennmcd = en_0206_rspp.ennmcd(+)
ORDER BY en_0002_dscdnm.dscd)
where rn <=100)