我要评论出现问题的原因和背景
oracle进行关联查询的时候因为字段存在多个用逗号切割的id，导致查询的过程中报无效数字或非法数字
问题复现1
新建表a
create table "a" (
id number not null,
name varchar2(255 byte)
)
insert into "a" values ('1', '上海');
insert into "a" values ('2', '北京');
insert into "a" values ('3', '广州');
insert into "a" values ('4', '深圳');新建表b
create table "b" (
id number not null,
aid varchar2(255 byte)
)
insert into "b" values ('1', '1,2,3');
insert into "b" values ('2', '1,2');
insert into "b" values ('3', '1,2,3,4');问题复现2
select * from a where id in (select aid from b where id = 3)
产生的原因:因为查询过程中，多个id放在了一个字段，这个字段不止有数字也有逗号,id是数字类型,所以数据库识别以后直接会报非法数字。
解决方法
先将子查询转成多行
with
split_string as (
select (select aid from b where id = 3)as string from dual
)
select
regexp_substr(string, '[^,]+', 1, level) as aid
from
split_string
connect by
regexp_substr(string, '[^,]+', 1, level) is not null;
再次查询a表数据
select * from a where id in ( with
split_string as (
select (select aid from b where id = 3)as string from dual
)
select
regexp_substr(string, '[^,]+', 1, level) as aid
from
split_string
connect by
regexp_substr(string, '[^,]+', 1, level) is not null)
将a表的名称转成一行
select listagg(name,',') within group (order by name) 关联流程 from a where id in ( with
split_string as (
select (select aid from b where id = 3)as string from dual
)
select
regexp_substr(string, '[^,]+', 1, level) as split_value
from
split_string
connect by
regexp_substr(string, '[^,]+', 1, level) is not null) 
以上就是oracle关联查询报invalid number错误的解决方法的详细内容，更多关于oracle报invalid number错误的资料请关注代码网其它相关文章！
发表评论