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;


 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;

 ---------   -----------   ----------
      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;

---------    -----------
    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
   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 ( ).
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 ;


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 site for more details.
This approach is explained below with an example;

SELECT studentid, LTRIM(SYS_CONNECT_BY_PATH(coursename,','))
       SELECT studentid,coursename,
                          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 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.

No comments:

Post a Comment