How do you resolve ORA-01427 single row subquery returns more than one row?

How do you resolve ORA-01427 single row subquery returns more than one row?

Dmytro offered this advice to resolve ORA-01427: Try to add and rownum=1 to your subquery conditions if you DO NOT care about the value from the list or DO sure that they are the same. A single row subquery returns only one row. It can be used with the equal comparison operators (=,<,>,<>, etc).

Can subquery return multiple rows?

Multiple-row subqueries are nested queries that can return more than one row of results to the parent query. Multiple-row subqueries are used most commonly in WHERE and HAVING clauses. Since it returns multiple rows, it must be handled by set comparison operators (IN, ALL, ANY).

How do I return more than one row in SQL?

Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows.

Which operator will not accept more than one value from the subquery?

A multiple-row subquery returns more than one row of data. The operators used in a single-row subqueries relational operators (=, <>, >, >=, <, <=) cannot be used in multiple-row subqueries. Instead, other operators must be used. True if the value does not equal any value in the table.

Does subquery return tabular results?

A scalar subquery returns a single value. A column subquery returns a single column of one or more values. A row subquery returns a single row of one or more values. A table subquery returns a table of one or more rows of one or more columns.

How do I stop subquery returns more than one row?

= can be used when the subquery returns only 1 value. For this example, make sure your “multiple row query” returns just one column. Something like SELECT ID FROM FOO will work, whereas SELECT ID, NAME FROM FOO or SELECT * FROM FOO won’t.

What are multi row subquery operators?

Answer: B. Multiple-row subqueries return more than one row of results. Operators that can be used with multiple-row subqueries include IN, ALL, ANY, and EXISTS. The multi row operators IN, ANY, ALL must be used with single row operators as shown in the option B.

Can a subquery return more than one row?

#1242 – Subquery returns more than 1 row. I have tried placing the COUNT statement in the subquery but I get an invalid syntax error. Show activity on this post. If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery.

How many rows can be returned from a SELECT query?

The first SELECT can return at most one row. The second SELECT can return at most one row because of the LIMIT 1. In the worst case scenario, the UNION will have two rows. The outer part of the query will receive either 0, 1, or 2 rows.

How can I get more than one result from a subquery?

Your two outer queries are structured to expect a single result from the their subqueries. But the way you have things structured, your subqueries might return more than one result. If you actually want more than one result, restructure it like this: where disease_id IN (subquery returning multiple rows…)

How to fix error 1242 subquery returns more than one row?

If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg: This query return error: SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2); This is good query: SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2); Share.