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.

As you already know, this sub-query to will result in returning multiple rows. So on executing the above query, the oracle exception “ORA-01427” will be returned.

As a fix, I can use the condition “and rownum = 1” to meet my requirement. But if suppose I want to know/display all the students with their course name from that sub-query… then?????

Yes. It’s possible to display multiple records in row with the following approaches.

Approach 1: Using WM_CAT in Workspace manager

In oracle 10g onwards, WM_SYS schema package uses STRAGG for work space management (http://docs.oracle.com/cd/B19306_01/appdev.102/b14253/long_intro.htm ).

We can use “WM_CAT” utility to concat the student details with course name in the above sub query. That is;

SQL> select wmsys.wm_concat(t2.coursename) from tblCourse t2 ; WMSYS.WM_CONCAT(T2.COURSENAME) ------------------------------------------------------------- COURSE:1,COURSE:2,COURSE:3,COURSE:4,COURSE:5, COURSE:6,COURSE:7,COURSE:8

Approach 2: Using the analytical function ROW_NUMBER()

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. Refer oracle sitehttp://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm for more details.

This approach is explained below with an example;

SELECT studentid, LTRIM(SYS_CONNECT_BY_PATH(coursename,',')) FROM (         SELECT studentid,coursename,        ROW_NUMBER() OVER (PARTITION BY studentid ORDER BY                            coursename ) -1 AS seq        FROM tblCourse      ) WHERE connect_by_isleaf = 1 CONNECT BY seq = PRIOR seq +1 AND studentid = PRIOR studentid START WITH seq = 1;

Approach 3: Using the AGGREAGE function XMLAgg ()

XMLAgg is an aggregate function provided by oracle in connection with XML related operations. Please refer http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions215.htm for more details. The original query can be rewritten with “XMLAgg” in the following way;

SELECT studentid      , RTRIM      ( XMLAGG (XMLELEMENT (ele, coursename || ',')        ORDER BY coursename).extract ('//text()')        , ',' ) AS CourseNames FROM tblCourse GROUP BY studentid;

With these alternatives we can show multiple results in a single row with oracle.