Oracle: Display Multiple Records in One Row
Solutions for “ORA-01427: single-row subquery returns more than one row”
Now if I want to retrieve information of particular “Course” for the students within certain criteria, I will throw the below query.
Here we are using the sub-query “select t2.coursename from tblCourse t2 where t2.studentid = studentid” to find out the course details.
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
)
|
Here we are using the sub-query “select t2.coursename from tblCourse t2 where t2.studentid = studentid” to find out the course details.