oracle关联查询报invalid number错误
出现问题的原因和背景
oracle进行关联查询的时候因为字段存在多个用逗号切割的id，导致查询的过程中报无效数字或非法数字
问题复现1
新建表A
1 2 3 4 5 6 7 8 9 | 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
1 2 3 4 5 6 7 8 | 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
1 | select * from A where id in (select aid from B where id = 3)
|
产生的原因:因为查询过程中，多个id放在了一个字段，这个字段不止有数字也有逗号,id是数字类型,所以数据库识别以后直接会报非法数字。
解决方法
先将子查询转成多行
1 2 3 4 5 6 7 8 9 10 | 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表数据
1 2 3 4 5 6 7 8 9 10 | 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表的名称转成一行
1 2 3 4 5 6 7 8 9 10 | 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)
|
