Oracle: WHERE ID IN () single-row subquery returns more than one row

0
votes
SELECT FIELD1, FIELD2...
FROM VIEW1
WHERE ID IN (SELECT DISTINCT ID FROM TABLE1 WHERE NAME_FIELD LIKE '%ABC%')
ORDER BY FIELD1, FIELD2;

When select from a view, one of the condition is this WHERE ID IN (subquery). The oracle error as in title is raised.

But if I ran the subquery first, then replace the subquery with the returned IDs, it is working.

Or if I attached the subquery inside of the view query, it is also working.

What have I done wrong? Thanks.

1
sqloracle
What if you try select * from VIEW1? – Aleksej
Thank you @Aleksej, I have spotted the problem. It's a sub-query within the view. A data quality issue. – Cal
When you check for an IN condition, select DISTINCT doesn't help - rather, it hurts. select DISTINCT is much more expensive than simple SELECT. Try it both ways and see which works better. – mathguy

1 Answers

0
votes

Use a cut operation instead:

SELECT FIELD1, FIELD2...
  FROM VIEW1
 WHERE EXISTS (SELECT 1 
                 FROM TABLE1 
                WHERE NAME_FIELD LIKE '%ABC%' 
                  AND VIEW1.ID = TABLE1.ID)
ORDER BY FIELD1, FIELD2;