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.