select regexp_substr('ED2WCSID_PH_20110324.txt','[0-9]+',5) from dual
结果:20110324
select regexp_substr('EDWCSID_PH_20110324.txt','[0-9]+') from dual
结果:20110324
1 case when
select distinct t.RPTID as rid,t.start_year as 起始年 ,t.start_month as 起始月 ,t.end_year as 截至年 , t.end_month
as 截至月,
( CASE WHEN t.reporttype = 'month' THEN '月报'
ELSE '年报'
END
)as 报表类型 ,( CASE WHEN t.reporthome = 'gf' THEN '股份'
ELSE '集团'
END
) as 报表归属, t.operationname as 修改人,t.operationtime as 修改日期 ,v.rvVersion as 版本
from REPORT_RATE t join RPTVERSION v on t.RPTVID=v.RVID left join reportinfo r on t.rptcode=r.rptcode order by t.rptid
2 decode
select distinct t.RPTID as rid,t.start_year as 起始年 ,t.start_month as 起始月 ,t.end_year as 截至年 , t.end_month
as 截至月,
( CASE WHEN t.reporttype = 'month' THEN '月报'
ELSE '年报'
END
)as 报表类型 ,( CASE WHEN t.reporthome = 'gf' THEN '股份'
ELSE '集团'
END
) as 报表归属, t.operationname as 修改人,t.operationtime as 修改日期 ,v.rvVersion as 版本
,decode(t.reporthome,'gf','股份','jt','集团','其他')
from REPORT_RATE t join RPTVERSION v on t.RPTVID=v.RVID left join reportinfo r on t.rptcode=r.rptcode order by t.rptid
3 repalce
select
replace(replace(t.reporthome,'jt','集团'),'gf','股份')
from report_rate t
as 截至月,
( CASE WHEN t.reporttype = 'month' THEN '月报'
ELSE '年报'
END
)as 报表类型 ,( CASE WHEN t.reporthome = 'gf' THEN '股份'
ELSE '集团'
END
) as 报表归属, t.operationname as 修改人,t.operationtime as 修改日期 ,v.rvVersion as 版本
from REPORT_RATE t join RPTVERSION v on t.RPTVID=v.RVID left join reportinfo r on t.rptcode=r.rptcode order by t.rptid
2 decode
select distinct t.RPTID as rid,t.start_year as 起始年 ,t.start_month as 起始月 ,t.end_year as 截至年 , t.end_month
as 截至月,
( CASE WHEN t.reporttype = 'month' THEN '月报'
ELSE '年报'
END
)as 报表类型 ,( CASE WHEN t.reporthome = 'gf' THEN '股份'
ELSE '集团'
END
) as 报表归属, t.operationname as 修改人,t.operationtime as 修改日期 ,v.rvVersion as 版本
,decode(t.reporthome,'gf','股份','jt','集团','其他')
from REPORT_RATE t join RPTVERSION v on t.RPTVID=v.RVID left join reportinfo r on t.rptcode=r.rptcode order by t.rptid
3 repalce
select
replace(replace(t.reporthome,'jt','集团'),'gf','股份')
from report_rate t
start with ONNECT BY PRIOR
1,
select t.ciid ,t.cicode,t.ciscode,t.ciname name,t.cispcode parentId,T.CORBELONG,
p.ciscode pscode,p.ciname pname,t.ciname
from corinfo t,(select * from corinfo ci where ci.civalid='1') p
where 1=1 and t.cispcode = p.cicode(+) AND T.CORBELONG ='gf'
and t.ciscode not like 'T%' and t.ciscode not like 'UT%' and t.ciscode not like 'LD%' and t.ciscode not like 'HW%'
order by t.ciscode desc ,
p.ciscode pscode,p.ciname pname,t.ciname
from corinfo t,(select * from corinfo ci where ci.civalid='1') p
where 1=1 and t.cispcode = p.cicode(+) AND T.CORBELONG ='gf'
and t.ciscode not like 'T%' and t.ciscode not like 'UT%' and t.ciscode not like 'LD%' and t.ciscode not like 'HW%'
order by t.ciscode desc ,
2,
select t.ciid ,t.cicode,t.ciscode, t.ciname name,T.cispcode parentId, p.ciscode pscode,p.ciname pname ,t.cilevel,b.cbfolder,t.ciname
from corinfo t,corboe b,(select * from corinfo ci where ci.civalid='1') p
where 1=1 and t.civalid = '1' and
t.CIID = b.CIID and b.CBVALID = 1 and t.cispcode = p.cicode(+)
and t.rvid is not null
AND T.CORBELONG ='gf'
and t.ciscode not like 'T%' and t.ciscode not like 'UT%' and t.ciscode not like 'LD%' and t.ciscode not like 'HW%'
start with t.cispcode = 0 CONNECT BY PRIOR t.cicode = t.cispcode ORDER BY t.ciname asc
from corinfo t,corboe b,(select * from corinfo ci where ci.civalid='1') p
where 1=1 and t.civalid = '1' and
t.CIID = b.CIID and b.CBVALID = 1 and t.cispcode = p.cicode(+)
and t.rvid is not null
AND T.CORBELONG ='gf'
and t.ciscode not like 'T%' and t.ciscode not like 'UT%' and t.ciscode not like 'LD%' and t.ciscode not like 'HW%'
start with t.cispcode = 0 CONNECT BY PRIOR t.cicode = t.cispcode ORDER BY t.ciname asc