已解决：ORA-00932:数据类型不一致:应为-,但却获得CLOB

1.问题原因

Oracle的sql中的使用函数返回的值的字段没有对应的类型，只是一段文本。所以在使用该字段关联的时候就会报错。

2.解决办法

使用to_char()方法将文本转成字符串类型即可。

示例

原sql：就是将selectfk中的多个值根据特地符号分割成多条记录，并与其他表关联查询其他信息。但是运行的时候就会出现该错误。ORA-00932:数据类型不一致:应为-,但却获得CLOB。因为REGEXP_SUBSTR()函数返回的是一段文本并不是字符串，所以关联的时候会取不到类型报错。所以使用to_char()将文本转成字符串。

SELECT
  zjb2.id	  	  as id,
  zjb2.perinfofk  as perinfofk,
  perinfo.name        as name,
  perinfo.code        as code,
  perinfo.cardnum     as cardnum,
  xingbie.name            as sexname,
  bumen1.name          as mgroufkname,
  bumen2.name          as newoufkname,
  to_char(zjb2.txdate,'yyyy-MM-dd')      as txdate
FROM (SELECT
 		zjb1.id   as id,
		REGEXP_SUBSTR(zjb1.selectfk, '[^,]+', 1 ,rownum)  as perinfofk,
		zjb1.oldoufk    as oldoufk,
		zjb1.newoufk    as newoufk,
		zjb1.txdate     as txdate
FROM 
(SELECT zjb.id,zjb.oldoufk,zjb.newoufk,zjb.selectfk,nvl(zjb.ddsj,zjb.txdate) as txdate
	FROM table0 zjb
) zjb1
connect by rownum <= regexp_count(zjb1.selectfk, ',') + 1) zjb2
left join yonghu.perinfo perinfo on perinfo.id = zjb2.perinfofk
left join base.zidian xingbie on xingbie.id = perinfo.sexid
left join base.bumen bumen1 on bumen1.id = zjb2.oldoufk
left join base.bumen bumen2 on bumen2.id = zjb2.newoufk
           

使用to_char()转换文本类型，使得可以用于关联字段。同时发现，因为rownum伪列的原因会出现重复行，所以在查询的时候使用distinct过滤。

完整sql如下

SELECT
  zjb2.id	  	  as id,
  zjb2.perinfofk  as perinfofk,
  perinfo.name        as name,
  perinfo.code        as code,
  perinfo.cardnum     as cardnum,
  xingbie.name            as sexname,
  bumen1.name          as mgroufkname,
  bumen2.name          as newoufkname,
  to_char(zjb2.txdate,'yyyy-MM-dd')      as txdate
FROM (SELECT DISTINCT
 		zjb1.id   as id,
		TO_CHAR(REGEXP_SUBSTR(zjb1.selectfk, '[^,]+', 1 ,rownum))  as perinfofk,
		zjb1.oldoufk    as oldoufk,
		zjb1.newoufk    as newoufk,
		zjb1.txdate     as txdate
FROM 
(SELECT zjb.id,zjb.oldoufk,zjb.newoufk,zjb.selectfk,nvl(zjb.ddsj,zjb.txdate) as txdate
	FROM table0 zjb
) zjb1
connect by rownum <= regexp_count(zjb1.selectfk, ',') + 1) zjb2
left join yonghu.perinfo perinfo on perinfo.id = zjb2.perinfofk
left join base.zidian xingbie on xingbie.id = perinfo.sexid
left join base.bumen bumen1 on bumen1.id = zjb2.oldoufk
left join base.bumen bumen2 on bumen2.id = zjb2.newoufk