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).

How do I fix single row subquery returns more than one row in Oracle?

Using IN Operator In practice, SELECT should use IN operator instead of = (equal operator) in order to accommodate more than one row returned by the subquery. SQL> select * from employees where department_id in (select department_id from departments where location_id = 1700);

What does single row subquery returns more than one row mean?

The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery.

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.

Why does Ora 01427 return more than one row?

ORA-01427: single-row subquery returns more than one row This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.

How to avoid ora-01427 in subquery statement?

More comparison conditions like ANY, SOME or ALL should also be helpful to accept more than one row in your statements so as to avoid ORA-01427 in subquery statement. 2. Using = (Equal) Operator

What causes Oracle subquery to return more than one row?

The ORA-01427 is a straightforward query error in Oracle. The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery.

Why does select statement return more than one row?

This is because the subquery in the SELECT statement returned more than one row for the predicate department_id, which does not comply with a singular value limited operator, the equal =. Consequently, the statement fails to continue and then throw ORA-01427 to notify developers.