高分求高手将sqlserver视图 修改为 oracle视图

Question

如题，直接上代码
CREATE VIEW [dbo].[View_GEnterMonitoringSummary]
AS
SELECT xx,xx,xx,xx,xx
FROM(SELECT bi.DirectID AS xx,
bi.BID AS xx,
bi.MYear AS xx,
sd.MMonth AS xx,
bi.Province AS xx,
bi.City AS xx,
bi.AreaName AS xx,
bi.AreaCode AS xx,
bi.TradeName AS xx,
bi.TradeCode AS xx,
bi.EnterName AS xx,
bi.OrgCode AS xx,
bi.EnterTypeName AS xx,
bi.EnterType AS xx,
bi.Longitude AS xx,
bi.Latitude AS xx,
bi.Province AS xx,
bi.City AS xx,
bi.County AS xx,
bi.Township AS xx,
bi.District AS xx,
bi.Contact AS xx,
bi.Tel AS xx,
bi.Fax AS xx,
bi.Zip AS xx,
case when bi.IfSM=1 then '是' else '否' end AS xx,
bi.SMDate AS xx,
bi.NotSMReason+bi.ONotSMReason AS xx,
case when bi.SMMode='1' then 'xx' when bi.SMMode='2' then 'xx' else 'xx' end AS xx,
bi.DelegationUnit AS xx,
case when bi.IfSMScheme=1 then '是' else '否' end AS xx,
case when bi.IfSMRecord=1 then '是' else '否' end AS xx,
case when bi.IfSMSchemeOpen=1 then '是' else '否' end AS xx,
case when bi.IfLastYearReport=1 then '是' else '否' end AS xx,
bi.ReportOpenDate AS xx,
(SELECT DicName FROM Sys_Dic WHERE DicType = '5' AND DicCode = mp.MpType) AS xx,
mp.MpType AS xx,
mp.MpName AS xx,
mp.MpCode AS xx,
mp.StandCode AS xx,
mp.StandName AS xx,
mp.MpSdate AS xx,
mp.MpEdate AS xx,
mi.MiName AS xx,
mi.MiCode AS xx,
case when mi.ifMonitor=1 then '是' else '否' end AS xx,
case when mi.MiTec=1 then 'xx' else 'xx' end AS xx,
mi.MiFre AS xx,
mi.SValue AS xx,
mi.MiIns AS xx,
mi.MiOTime AS xx,
(SELECT count(*) FROM MpInfo_SpChild ms WHERE ms.MpID=mp.MpID AND ms.MYear=bi.MYear AND ms.MMonth=sd.MMonth) AS 天数,
(SELECT sum(mcn.MNum) FROM MiInfo_Child_Num mcn, MiInfo_Child mc WHERE mcn.McID=mc.McID AND mc.MiId=mi.MiID AND mcn.MMonth=sd.MMonth) AS xx,
(SELECT count(*) FROM AccountData ad, MiInfo_Child mc WHERE ad.MiID=mi.MiID AND ad.MiID=mc.MiID AND ad.MiFre=mc.MiFre AND ad.MMonth=sd.MMonth
AND DateDiff (day,mc.SDate,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end) >= 0 AND DateDiff (day,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end,mc.EDate) >= 0
AND (ad.MState=1 OR ad.MState=2)) AS xx,
(SELECT count(*) FROM AccountData ad, MiInfo_Child mc WHERE ad.MiID=mi.MiID AND ad.MiID=mc.MiID AND ad.MiFre=mc.MiFre AND ad.MMonth=sd.MMonth
AND DateDiff (day,mc.SDate,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end) >= 0 AND DateDiff (day,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end,mc.EDate) >= 0
AND (ad.MState=4)) AS xx,
(SELECT count(*) FROM AccountData ad, MiInfo_Child mc WHERE ad.MiID=mi.MiID AND ad.MiID=mc.MiID AND ad.MiFre=mc.MiFre AND ad.MMonth=sd.MMonth
AND DateDiff (day,mc.SDate,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end) >= 0 AND DateDiff (day,cast(bi.MYear as varchar) + '-' + cast(ad.MMonth as varchar) + '-' + case when ad.MiFre='日' then cast (ad.Num AS VARCHAR) when ad.MiFre='周' then cast(day(DateAdd(week,ad.Num,cast(cast(bi.MYear AS VARCHAR)+'-1-1' as datetime))) AS VARCHAR) else '1' end,mc.EDate) >= 0
AND ad.MState=1) AS xx
FROM MiInfo mi, MpInfo mp,BaseInfo bi,(SELECT DicCode AS MMonth FROM Sys_Dic sd WHERE sd.DicType='month') sd
WHERE mi.MpID=mp.MpID AND mp.BID=bi.BID AND (mp.MpType=2 OR mp.MpType=3 OR mp.MpType=7) AND bi.EnterType like '%2%')v
LEFT JOIN (select * from DirectInfo_ReportChild where IsEnable = 1 AND MType='2') dirc on v.[xx]=dirc.DirectID and v.[xx]=dirc.MYear and v.[月份]=dirc.MMonth
WHERE (dirc.MState is null)

Answer 1

使用PowerDesigner的逆向功能，先导入SQL，然后转换。
http://www.2cto.com/database/201210/161178.html