Pages

February 22, 2012

Oracle: Display Multiple Records in One Row....


Oracle: Display Multiple Records in One Row

Solutions for “ORA-01427: single-row subquery returns more than one row

Recently I got the oracle exception “ORA-01427: single-row subquery returns more than one row”, when I tried to execute one of my SQL queries in Oracle which contains another sub query. After googling the root cause and possible ways, I was able to figure out the culprit rows that makes this error.  The solutions I found are described below;

Actually, this is also a solution to display multiple records in one row in Oracle…..
Let us first reproduce the above error with simple example; say, I have two tables with the following details;

TableName
ColumnNames
tblStudent
studentid,studentname,studentage
tblCourse
studentid,coursename

 The simple select query from the above table’s tblStudent and tblCourse will result;
SQL> select t1.studentid, t1.studentname, t1.studentage from  tblStudent t1;

 STUDENTID   STUDENTNAME   STUDENTAGE
 ---------   -----------   ----------
      1        STUDENT:1        30
      2        STUDENT:2        30
      3        STUDENT:3        30
      4        STUDENT:4        30
      5        STUDENT:5        30
      6        STUDENT:6        30
      7        STUDENT:7        30
      8        STUDENT:8        30
      9        STUDENT:9        30
     10        STUDENT:10       30

10 rows selected

SQL> select t2.studentid, t2.coursename from tblCourse t2;

STUDENTID     COURSENAME
---------    -----------
    1          COURSE:1
    1          COURSE:2
    1          COURSE:3
    1          COURSE:4
    3          COURSE:5
    3          COURSE:6
    3          COURSE:7
    3          COURSE:8
    3          COURSE:9
    3          COURSE:10

10 rows selected


Now if I want to retrieve information of particular “Course” for the students within certain criteria, I will throw the below query.

select studentId as Student_Id,
studentname as Student_Name,
(
select t2.coursename from tblCourse t2
      where t2.studentid = studentid
) as Student_Course
from
(
   select t1.studentid, t1.studentname, t1.studentage 
   from tblStudent t1  where t1.studentid <= 5
)


Here we are using the sub-query “select t2.coursename from tblCourse t2 where t2.studentid = studentid” to find out the course details.