single-row subquery returns more than one row oracle

oracle, sql

Solution

Have you thought about using a `JOIN` instead of the subquery:

select 
  t.test_case_id,
  t.status,
  case when t.status = 'FAIL' then l.defect_id  
    end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id

See SQL Fiddle with Demo

This will return both records, then you can decide which item to return in your final result.

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           20 |   FAIL |        500 |
|           30 |   FAIL |        500 |
|           30 |   FAIL |        400 |
|           10 |   PASS |     (null) |

Based on your comment, if you are using Oracle 11g, then you can use the `LISTAGG()` function to combine the records into one row:

select 
  t.test_case_id,
  t.status,
  case 
    when t.status = 'FAIL' 
    then listagg(l.defect_id, ', ')
          within group (order by l.defect_id)
  end as defect1_id
from test t
left join link1 l
  on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status

See SQL Fiddle with Demo

Result:

| TEST_CASE_ID | STATUS | DEFECT1_ID |
--------------------------------------
|           10 |   PASS |     (null) |
|           20 |   FAIL |        500 |
|           30 |   FAIL |   400, 500 |

Problem

I have three tables like below: Test ``` +--------------+--------+ | Test_Case_ID | Status | +--------------+--------+ | 10 | PASS | | 20 | FAIL | | 30 | FAIL | +--------------+--------+ ``` Defect ``` +-----------+ | Defect_ID | +-----------+ | 500 | | 400 | +-----------+ ``` and link1 ``` +--------------+-----------+ | Test_Case_ID | Defect_ID | +--------------+-----------+ | 20 | 500 | | 30 | 500 | | 30 | 400 | +--------------+-----------+ ``` I am trying the below query ``` select test.test_case_id, test.status, case when test.status = 'FAIL' then (select link1.defect_id from link1 where test.test_case_id = link1.test_case_id) end as defect1_id from test test ``` I get the below error "Error 12/20/2012 10:05:17 AM 0:00:00.093 Toad for Data Analysts: ORA-01427: single-row subquery returns more than one row 1 78 " Is there a way to retrieve both the records for "30" from the link table? Because i want to display that test case 30 is failing because of defect 500 & 400. Thanks so much

Original source