user2093576 user2093576 - 7 months ago 21
SQL Question

Oracle - single-row subquery returns more than one row , Need to fetch all the rows

The following query returns


"single-row subquery returns more than one row"


select * from sampleTable
where
status = 'A'
and (SELECT SUBSTR(some_code_column, 1, 4) from sampleTable) = 9999


I need to fetch all the rows of the table where status is A and All the rows with
SUBSTR(some_code_column, 1, 4) = 9999

How to change the query so that it fetches the required result?

Answer

No need for that sub-select, simply AND the conditions:

select * from  sampleTable
             where 
             status = 'A' 
             and SUBSTR(some_code_column, 1, 4)  = 9999