Helpful Information
 
 
Category: Oracle Development
Help - ORA-01427: single-row subquery returns more than one row

What i'm trying to do is count the number of rows those sub-queries return.

As i understand it, that error happens when more than 1 row is returned.

How else would I go about achieving what I need?

Here is my query:
SQL> SELECT
2 a.ssg_nme,
3 b.sftw_prod_nme,
4 COUNT((SELECT db_srvr_id FROM inv.db_srvr_t WHERE node_id = (SELECT node_id FROM inv.node_t WH
ERE node_loc_cde IN (5, 7, 8)))),
5 COUNT((SELECT db_srvr_id from inv.db_srvr_t WHERE row_status_cde = 2)),
6 COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
7 FROM
8 inv.cde_ssg_t a, inv.sftw_prod_t b, inv.db_srvr_t c, inv.appl_t d, inv.db_t e
9 WHERE d.appl_id = e.appl_id
10 AND e.db_srvr_id = c.db_srvr_id
11 AND d.appl_ssg_cde IN (6, 9)
12 GROUP BY b.sftw_prod_nme, a.ssg_nme;
COUNT((SELECT db_id from inv.db_t WHERE row_status_cde = 2 AND db_srvr_id = 1207))
*
ERROR at line 6:
ORA-01427: single-row subquery returns more than one row

Have you tried using IN instead of `=` for the node_id?

I am trying to execute an UPDATE from a field that may have more than row. So I get ORA-01427: single-row subquery returns more than one row.


UPDATE test
SET ts_user_02
= (SELECT tc_user_01
FROM testcycl
WHERE test.ts_test_id = testcycl.tc_test_id
AND testcycl.tc_user_01 <> ' '
AND ts_user_02 IS NULL)
WHERE EXISTS
(SELECT tc_user_01
FROM testcycl
WHERE test.ts_test_id = testcycl.tc_test_id
AND testcycl.tc_user_01 <> ' '
AND ts_user_02 IS NULL)

test.ts_test_id is the primary key. There are multiple rows in testcycl.tc_test_id with the same number and different data in testcycl.tc_user_01. It has been decided to take the first instance of testcycl.tc_test_id and update test. ts_user_02 with testcycl.tc_user_01.

What can I do to make it work? I tried FIRST ROW and ROWNUM = 1.

UPDATE test
SET ts_user_02
= (SELECT MIN(tc_user_01)
FROM testcycl
WHERE test.ts_test_id = testcycl.tc_test_id
AND testcycl.tc_user_01 <> ' '
AND ts_user_02 IS NULL)
WHERE EXISTS
(SELECT tc_user_01
FROM testcycl
WHERE test.ts_test_id = testcycl.tc_test_id
AND testcycl.tc_user_01 <> ' '
AND ts_user_02 IS NULL)










privacy (GDPR)