CODEWITHSUNDEEP
sqloracle-database
kiara
kiara

Reputation: 11

Ambiguity result in SQL query

There's a query -

select ID from table1;

This gives result -

ID
101
102
103
104

When I use the query -

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

It gives an error:

java.sql.SQLSyntaxErrorException: ORA-01722: invalid number.

Whereas, when I use the query -

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

This gives correct result.

How can I solve this?

Upvotes: 0

Views: 141

Answers (3)

Dario
Dario

Reputation: 470

Just to clarify my comments in this question.

Can you provide the result of this query select * from TABLE1 where CAST(ID AS NUMBER DEFAULT NULL ON CONVERSION ERROR) is NULL and ID IS NOT NULL;

The only way that I found to reproduce the issue described here is having non-numeric values in table1. (even if at the beginning we are showing 4 numeric values then we are talking about "it's just not 4 values, its 1000s")

testcase

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;

In this test-case, the only query generating an error ORA-01722: invalid number is

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

it corresponds at the behavior described.

This is why my suggestion is to do an implicit cast and avoid the errors as already suggested in this thread

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;

and try to find the non-numeric values

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

Upvotes: 0

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Your query is working fine. Please check data type of ID column in both tables. They should match. One might be number and another might be varchar in your tables.

DB-Fiddle:

Schema and insert statements:

 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');

Query:

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

Output:

ID STATUS
102 0
109 1

db<>fiddle here

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415820

Looks like Table2 uses a text (varchar, nvarchar, etc) type for the ID, where Table1 uses int for the ID. You should be able handle this by casting one to other, but really the best option here is fixing the table definitions to match (which might also mean changing the application).

Even then, you'll be better off with a JOIN or NOT EXISTS() query, instead of NOT IN()

Upvotes: 1

Related Questions