java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

ORA-01427: 单行子查询返回多个行

sql语句如下：

select h.operator,
       to_char(h.operate_tm, ‘yyyy-mm-dd hh24:mi:ss‘) operate_tm,
       (select t.res_label name
          from sysmngdba.res@msp t
         where t.id = h.system_id) system_id,
       (select t.res_label name
          from sysmngdba.res@msp t
         where t.id = h.module_id) module_id,
       (select index_value
          from int_sys_param
         where index_type = ‘operateType‘
           and index_code = h.operate_type) operate_type,
       h.operate_page,
       h.tab_nm,
       DECODE(h.status, ‘0‘, ‘??‘, ‘1‘, ‘??‘, ‘2‘, ‘???????????‘) status,
       DECODE(h.sensitive_Id, ‘1‘, ‘????‘, ‘2‘, ‘??‘, ‘3‘, ‘??‘) sensitive_Id,
       h.ip_address,
       h.note,
       h.session_id,
       h.transaction_id,
       h.operator || ‘,‘ || nvl(h.operate_page, ‘ ‘) || ‘,‘ ||
       nvl(h.session_id, 0) || ‘,‘ || nvl(h.transaction_id, 0)
  from (select a.operator,
               a.operate_tm,
               a.system_id,
               a.module_id,
               a.operate_type,
               a.operate_page,
               a.tab_nm,
               a.status,
               a.sensitive_Id,
               a.ip_address,
               a.note,
               a.session_id,
               a.transaction_id
          from process_log a
        union all
        select b.operator,
               b.operate_tm,
               b.system_id,
               b.module_id,
               b.operate_type,
               b.operate_page,
               b.tab_nm,
               b.status,
               b.sensitive_Id,
               b.ip_address,
               b.note,
               b.session_id,
               b.transaction_id
          from iigsmdba.parameter_log b) h
 where 1 = 1
   and operate_tm >=
       to_date(‘2015-03-06 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)
   and operate_tm <=
       to_date(‘2015-03-09 23:59:59‘, ‘yyyy-mm-dd hh24:mi:ss‘)

解决办法：

首先要确认是哪个子查询引起的，以上面的sql为例

在这三条在子查询中加入rownum<2, 限制返回一行数据。可以成功查询。

        select t.res_label name
          from sysmngdba.res@msp t
         where t.id = h.system_id and rownum<2) system_id,
       (select t.res_label name
          from sysmngdba.res@msp t
         where t.id = h.module_id and rownum<2) module_id,
       (select index_value
          from int_sys_param
         where index_type = ‘operateType‘
           and index_code = h.operate_type and rownum<2) operate_type,

然后依次去掉 rownum < 2，看看具体是哪一条引起的。

最后排查是下面这条语句存在多条数据：

select index_value
          from int_sys_param
         where index_type = ‘operateType‘
and index_code = h.operate_type