SQL查询中出现歧义

Question

有一个查询-

select ID from table1; 

这给出了结果-

ID101102103104

当我使用查询时-

select ID, 
CASE WHEN (ID NOT IN (SELECT ID FROM TABLE1)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2

它给出一个错误：

java.sql.SQLSyntaxErrorException：ORA-01722：无效数字。

然而，当我使用查询-时

select ID, 
CASE WHEN (ID NOT IN ('101','102','103','104')) THEN '1' ELSE '0' END STATUS 
FROM TABLE2

这给出了正确的结果。

我该如何解决这个问题？

Answer 1

看起来Table2使用文本(varchar、nvarchar等(类型作为ID，其中Table1使用int作为ID。您应该能够通过将一个转换为另一个来处理此问题，但实际上最好的选择是修复表定义以匹配(这也可能意味着更改应用程序(。

即便如此，使用JOIN或NOT EXISTS()查询而不是NOT IN()会更好

Answer 2

您的查询运行良好。请检查两个表中ID列的数据类型。它们应该匹配。一个可能是数字，另一个可能在您的表中是varchar。

DB Fiddle:

模式和插入语句：

create table table1 (id int);

insert into table1 values(101);
insert into table1 values(102);
insert into table1 values(103);
insert into table1 values(104);

create table table2 (id int, STATUS varchar(50));

insert into table2 values(102,'A');
insert into table2 values(109,'B');

查询：

select ID, CASE WHEN (ID NOT IN (SELECT ID FROM table1)) THEN '1' ELSE '0' END STATUS FROM table2

输出：

ID	STATUS
102	0
109	1

只是为了澄清我对这个问题的看法。

您能提供此查询的结果吗？从TABLE1中选择*，其中CAST(ID AS NUMBER DEFAULT NULL ON CONVERSION ERROR(为NULL，ID为不为空；

我发现重现此处描述的问题的唯一方法是在表1中使用非数值。(即使一开始我们显示的是4个数值，那么我们谈论的是"它不是4个值，而是1000"(

测试用例

drop table table1;
drop table table2;
create table table1 (id VARCHAR2(100));

insert into table1 values(101);
insert into table1 values(102);
insert into table1 values(103);
insert into table1 values(104);
insert into table1 values('MY BAD');
create table table2 (id NUMBER);
insert into table2 values(102);
insert into table2 values(109);

select ID, 
CASE WHEN (ID NOT IN (101,102,103,'104')) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;
select ID, 
CASE WHEN (ID NOT IN ('101','102','103','104')) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;
select ID, 
CASE WHEN (ID NOT IN (101,102,103,104)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;

select ID, 
CASE WHEN (ID NOT IN (SELECT ID FROM TABLE1)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;
select ID, 
CASE WHEN (ID NOT IN (SELECT CAST(ID AS NUMBER DEFAULT NULL ON CONVERSION ERROR) FROM TABLE1)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;

在这个测试用例中，唯一生成错误ORA-01722的查询：无效号码是

select ID, 
CASE WHEN (ID NOT IN (SELECT ID FROM TABLE1)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;

它对应于所描述的行为。

这就是为什么我的建议是进行隐式强制转换，并避免这个线程中已经建议的错误

select ID, 
CASE WHEN (ID NOT IN (SELECT CAST(ID AS NUMBER DEFAULT NULL ON CONVERSION ERROR) FROM TABLE1)) THEN '1' ELSE '0' END STATUS 
FROM TABLE2;

并尝试找到非数值

Select * from TABLE1 where
CAST(ID AS NUMBER DEFAULT NULL ON CONVERSION ERROR) is NULL and ID IS NOT NULL;