CODEWITHSUNDEEP
sqloracle-database
javanoob
javanoob

Reputation: 6410

single-row subquery returns more than one row oracle

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

Upvotes: 4

Views: 17979

Answers (2)

Taryn
Taryn

Reputation: 247670

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 |

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You have two rows in the link table that have values of "30". This is your problem.

Which of these rows do you want?

To fix the subquery, you can either say select max(link1.defect_id) or add and rownum = 1 to the where clause.

What you want is probably a bi more complicated. How about this version, which concatenates the defects into a string:

select t.test_case_id, t.status,
       listagg(cast(l.defect_id as varchar(32)) within group (order by l.defect_id) as defects
from test t left join
     link1 l
     on t.test_case_id = l.test_case_id
group by t.test_case_id, t.status

You don't specify the version of Oracle. If listagg is not available, then wm_concat probably is. Here is a reference on different ways to concat strings in an aggregation in Oracle.

Upvotes: 4

Related Questions