CODEWITHSUNDEEP
sqloracle-database
user2093576
user2093576

Reputation: 3092

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?

Upvotes: 1

Views: 117

Answers (3)

NishantMittal
NishantMittal

Reputation: 537

You want both the conditions to get true or any of them?? In case any of them then please use OR instead of AND

Upvotes: 0

Matt
Matt

Reputation: 15071

This was before you clarified you wanted to return data that satisfies both conditions not one or the other.

I would use a UNION in this scenario.

SELECT *
FROM sampleTable
WHERE status = 'A'
UNION
SELECT *
FROM sampleTable
WHERE SUBSTR(some_code_column, 1, 4)  = 9999

More reading on performance here https://stackoverflow.com/a/13866221/2641576

Upvotes: 2

jarlh
jarlh

Reputation: 44766

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

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

Upvotes: 1

Related Questions