我现在可以转一点点: charinde->instr substring->substr cast是转换函数,把数字转换成字符,或把字符转化成数字 case when oracle好像也有吧, 谢谢了
CREATE VIEW TPersonOld AS select ParChildID AutoId, YearStartcase=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))), YearEnd=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)- instr(ParData,'-')))) from TParameterMedicareChild where ParID= ( select ParID from TParameterMedicare where ParCode='Par_nljd' ) go
我转了一下,但是提示说找不到from. CREATE OR REPLACE VIEW VPersonOld AS select ParChildID AutoId, YearStart=case instr(ParData,'-')>0 when 0 then '' else to_number(substr(ParData,1,instr(ParData,'-')-1)) end, YearEnd=case instr(ParData,'-')>0 when 0 then '' else to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-instr(ParData,'-'))) end from TParameterMedicareChild where ParID= ( select ParID from TParameterMedicare where ParCode='Par_nljd' )
to ORARichard(没钱的日子好难过啊) , 谢谢了,可是提示找不到from CREATE or replace VIEW TPersonOld AS select ParChildID AutoId, YearStartcase=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))), YearEnd=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1 ,length(ParData)-instr(ParData,'-')))) from TParameterMedicareChild where ParID= ( select ParID from TParameterMedicare where ParCode='Par_nljd' )
CREATE or replace VIEW TPersonOld AS select ParChildID AutoId, decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))) YearStartcase, decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-instr(ParData,'-')))) YearEnd from TParameterMedicareChild where ParID= ( select ParID from TParameterMedicare where ParCode='Par_nljd' )这样可以了谢谢了!
CREATE VIEW TPersonOld AS select ParChildID AutoId, decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))) YearStartcase, decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)- instr(ParData,'-')))) YearEnd from TParameterMedicareChild where ParID= ( select ParID from TParameterMedicare where ParCode='Par_nljd' ) go
charinde->instr
substring->substr
cast是转换函数,把数字转换成字符,或把字符转化成数字
case when oracle好像也有吧,
谢谢了
AS
select ParChildID AutoId,
YearStartcase=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))),
YearEnd=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-
instr(ParData,'-'))))
from TParameterMedicareChild
where ParID=
( select ParID from TParameterMedicare
where ParCode='Par_nljd'
)
go
CREATE OR REPLACE VIEW VPersonOld
AS
select ParChildID AutoId,
YearStart=case instr(ParData,'-')>0 when 0 then '' else
to_number(substr(ParData,1,instr(ParData,'-')-1))
end,
YearEnd=case instr(ParData,'-')>0 when 0 then '' else
to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-instr(ParData,'-')))
end
from TParameterMedicareChild
where ParID=
( select ParID from TParameterMedicare
where ParCode='Par_nljd'
)
谢谢了,可是提示找不到from
CREATE or replace VIEW TPersonOld
AS
select ParChildID AutoId,
YearStartcase=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))),
YearEnd=decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1
,length(ParData)-instr(ParData,'-'))))
from TParameterMedicareChild
where ParID=
( select ParID from TParameterMedicare
where ParCode='Par_nljd'
)
AS
select ParChildID AutoId,
decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))) YearStartcase,
decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-instr(ParData,'-')))) YearEnd
from TParameterMedicareChild
where ParID=
( select ParID from TParameterMedicare
where ParCode='Par_nljd'
)这样可以了谢谢了!
AS
select ParChildID AutoId,
decode(instr(ParData,'-'),0,'',to_number(substr(ParData,1,instr(ParData,'-')-1))) YearStartcase,
decode(instr(ParData,'-'),0,'',to_number(substr(ParData,instr(ParData,'-')+1,length(ParData)-
instr(ParData,'-')))) YearEnd
from TParameterMedicareChild
where ParID=
( select ParID from TParameterMedicare
where ParCode='Par_nljd'
)
go