cast函数的用法案例

最近一开发人员在创建物化视图的时候抛了如下的异常"ORA-01723: 不允许长度为 0 的列".语句如下:

create materialized view v_service
as
select '2' as SERVSOURCE,v1.companycode as icpcode,v1.CompanyName as spname,v1.ShortName as spshortname,'' as SERVIDALIAS,v2.ServiceCode as icpservid,v2.ProductName as servname,
v2.ProductStatus as servstatus,'' as ServAttr,'' as umflag,v2.ServiceType as servtype,'' as UsageDesc,'' as wwwurl,'' as introURL,v2.ChargeType as ChargeType,
v2.Fee as Price,v2.ChargeDesc,'' as StartTime,'' as EndTime,v2.Description as servdesc,
'' as csrtel,'' as spurl,ACCESSMODEID,null as FREEUSETYPE,'' as OFFLINEDESC,null as OFFLINESTATE,v2.OfflineDate as OFFLINETIME,
null as FREEUSECOUNT,-1 as ONDEMANDFLAG,null as SERVICELOGO,null as GROUPTYPE,'' as SERVGROUPID,'' as TYPE,null as SPECORDERFLAG,
'' as SPECORDERURL,'' as brand,'' as demoURL,-1 as SERVCATID,'' as freeurl,'1' as paymode,'' as linkurl,'' as linkservid,'1' as linkservsource,v1.companycode||'_'||v2.ServiceCode as pksid,v2.LUPDDate
from V_OM_Company v1,V_OM_PRODUCT v2
where v1.CompanyID = v2.ProviderCOID and v2.ProductStatus <> '1' and ACCESSMODEID = '10';

可以看到用了很多null as 和'' as从而导致出现列长度为0.

修改后的语句用到了函数cast,该函数的格式为cast(exp as ),于是创建该mv可改为如下形式:

create materialized view v_service
as
select '2' as SERVSOURCE,v1.companycode as icpcode,v1.CompanyName as spname,v1.ShortName as spshortname,cast(null as varchar2(10)) as SERVIDALIAS,v2.ServiceCode as icpservid,v2.ProductName as servname,
v2.ProductStatus as servstatus,cast(null as varchar2(10)) as ServAttr,cast(null as varchar2(10)) as umflag,v2.ServiceType as servtype,cast(null as varchar2(10)) as UsageDesc,cast(null as varchar2(10)) as wwwurl,cast(null as varchar2(10)) as introURL,v2.ChargeType as ChargeType,
v2.Fee as Price,v2.ChargeDesc,cast(null as varchar2(10)) as StartTime,cast(null as varchar2(10)) as EndTime,v2.Description as servdesc,
cast(null as varchar2(10)) as csrtel,cast(null as varchar2(10)) as spurl,ACCESSMODEID,cast(null as varchar2(10)) as FREEUSETYPE,cast(null as varchar2(10)) as OFFLINEDESC,cast(null as varchar2(10)) as OFFLINESTATE,v2.OfflineDate as OFFLINETIME,
cast(null as varchar2(10)) as FREEUSECOUNT,-1 as ONDEMANDFLAG,cast(null as varchar2(10)) as SERVICELOGO,cast(null as varchar2(10)) as GROUPTYPE,cast(null as varchar2(10)) as SERVGROUPID,cast(null as varchar2(10)) as TYPE,cast(null as varchar2(10)) as SPECORDERFLAG,
cast(null as varchar2(10)) as SPECORDERURL,cast(null as varchar2(10)) as brand,cast(null as varchar2(10)) as demoURL,-1 as SERVCATID,cast(null as varchar2(10)) as freeurl,'1' as paymode,cast(null as varchar2(10)) as linkurl,cast(null as varchar2(10)) as linkservid,'1' as linkservsource,v1.companycode||'_'||v2.ServiceCode as pksid,v2.LUPDDate
from V_OM_Company v1,V_OM_PRODUCT v2
where v1.CompanyID = v2.ProviderCOID and v2.ProductStatus <> '1' and ACCESSMODEID = '10';

创建成功!

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/38542/viewspace-909694/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/38542/viewspace-909694/