求您转化条SQL(SQLSERVER->ORACLE)
select * from
( select a.DisHty1+case left(ND,1) when '4' then '年度' when '2' then '中期' end as [报告期],
case right(FA,1) when '1' then '正式' when '2' then '预案' end as [方案],
isnull(cast(cast(MFRatio14 as numeric(9,3)) as varchar),'--') as [每股收益(元)],
[10股分红(元)] = isNull(cast(cast((select top 1 DisHty3 from DISHTY where symbol=a.symbol and DISHTY1=a.DISHTY1
and left(DisHty2,1)=ND and right(DisHty2,1)=FA and right(left(DisHty2,2),1)='1') as numeric(9,2)) as varchar),'--'),
[10股送股(股)] = isnull(cast(cast((select DisHty7 from DISHTY where symbol=a.symbol and DISHTY1=a.DISHTY1
and left(DisHty2,1)=ND and right(DisHty2,1)=FA and right(left(DisHty2,2),1)='5') as numeric(9,2)) as varchar),'--'),
[10股转增(股)] = isnull(cast(cast((select DisHty8 from DISHTY where symbol=a.symbol and DISHTY1=a.DISHTY1
and left(DisHty2,1)=ND and right(DisHty2,1)=FA and right(left(DisHty2,2),1)='8')as numeric(9,2)) as varchar),'--'),
replace(convert(char(10),DisHty12,120),'1900-01-01','--') as [登记日],replace(convert(char(10),DisHty13,120),'1900-01-01','--') as [除权日],
[红利支付率(%)] = isnull(cast(cast(case when b.MFRatio14=0 or b.MFRatio14 is null then null else
(select DisHty3 from DISHTY where symbol=a.symbol and DISHTY1=a.DISHTY1 and left(DisHty2,1)=ND
and right(DisHty2,1)=FA and right(left(DisHty2,2),1)='1')*10/b.MFRatio14 end as numeric(9,2)) as varchar),'--')
from
(select DisHty1,left(DisHty2,1) as ND,right(DisHty2,1) as FA,symbol,companyCode,DisHty12,DisHty13
from DISHTY a where symbol='600110' and right(left(DisHty2,2),1) in ('1','5','8')
and right(DisHty2,1) = (
select case when min(right(DisHty2,1)) = 1 then min(right(DisHty2,1)) else max(right(DisHty2,1)) end
from DisHty where symbol = a.symbol and DisHty1 = a.DisHty1 AND right(left(DisHty2,2),1) in ('1','5','8'))
group by symbol,companyCode,DisHty12,DisHty13,DisHty1,left(DisHty2,1),right(DisHty2,1)) a
left join MFRatio b on a.companycode=b.companycode and year(b.ReportDate)=a.DisHty1
and month(b.ReportDate)/3=a.ND ) a
where not ( [每股收益(元)] is null and [10股分红(元)] is null
and [10股送股(股)] is null and [10股转增(股)] is null )
order by left([报告期],4) desc,[登记日] desc
我转了半天没转成功。其中有几个问题:
1、针对SQLSERVER的left()和right(),在ORACLE中要如何转化?
2、针对SQLSERVER的时间函数:month(),year(),day(),在ORACLE中要如何转化?
3、针对SQLSERVER的:isNull 在ORACLE中要如何转化?
4、针对SQLSERVER的:“[红利支付率(%)] = isnull(cast(cast(case when ...”在ORACLE中要如何转化?类似与AS么?
如果前辈能帮忙转化下就好了,小弟再次360度跪谢了!!