我有一段sql代码,会产生怪异的“ORA-01722: 无效数字”错误,代码如下:select a.doc_id as msg_id,
1 as msg_type,
a.title,
a.title as summary,
b.name as "from",
cast(b.sjzx_code as varchar(4)) as from_id,
a.upload_date as publish_time,
'http://www.csc108.com/main.jsp?menuId=212&level=1&url=/GsdtContent.jsp?docId='||a.doc_id as url
from cms_doc_single_attr a,branch b
where SUBSTR(a.editor,7,3)=b.branchid
and doc_id in (select doc_id from cms_doc_category_map where cat_id=1969) and a.doc_id>367394我发现,如果我做如下如下改动:
把数字367394改成36739,则运行成功。另外,还可以这样解决:加一个a.doc_id!=0即可,代码如下:
select a.doc_id as msg_id,
1 as msg_type,
a.title,
a.title as summary,
b.name as "from",
cast(b.sjzx_code as varchar(4)) as from_id,
a.upload_date as publish_time,
'http://www.csc108.com/main.jsp?menuId=212&level=1&url=/GsdtContent.jsp?docId='||a.doc_id as url
from cms_doc_single_attr a,branch b
where SUBSTR(a.editor,7,3)=b.branchid
and doc_id in (select doc_id from cms_doc_category_map where cat_id=1969) and a.doc_id!=0 and a.doc_id>367394
太怪异了!
有哪位高手知道是为什么吗?
1 as msg_type,
a.title,
a.title as summary,
b.name as "from",
cast(b.sjzx_code as varchar(4)) as from_id,
a.upload_date as publish_time,
'http://www.csc108.com/main.jsp?menuId=212&level=1&url=/GsdtContent.jsp?docId='||a.doc_id as url
from cms_doc_single_attr a,branch b
where SUBSTR(a.editor,7,3)=b.branchid
and doc_id in (select doc_id from cms_doc_category_map where cat_id=1969) and a.doc_id>367394我发现,如果我做如下如下改动:
把数字367394改成36739,则运行成功。另外,还可以这样解决:加一个a.doc_id!=0即可,代码如下:
select a.doc_id as msg_id,
1 as msg_type,
a.title,
a.title as summary,
b.name as "from",
cast(b.sjzx_code as varchar(4)) as from_id,
a.upload_date as publish_time,
'http://www.csc108.com/main.jsp?menuId=212&level=1&url=/GsdtContent.jsp?docId='||a.doc_id as url
from cms_doc_single_attr a,branch b
where SUBSTR(a.editor,7,3)=b.branchid
and doc_id in (select doc_id from cms_doc_category_map where cat_id=1969) and a.doc_id!=0 and a.doc_id>367394
太怪异了!
有哪位高手知道是为什么吗?
貌似字符串类型与数据类型比较出错?
可能某一行的数据有问题,可能原因较多,比如sjzx_code 长度大于4,SUBSTR(a.editor, 7, 3)不能转换为数字等。
把数字367394改成36739没出错,是因为游标还没读到那一行。
我也怀疑过是某一行数据有错误,所以用doc_id!=0来限制,果然好了。但是,问题是数据库中并没有doc_id=0的记录。
高手!真的是这个原因!
sjdz_code有null的。有的SUBSTR(a.editor, 7, 3)的值不能转换为数字。我原来的解决方案也不是一劳永逸。
怎么解决呢?请高手给指点指点!