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 [email protected] t
         where t.id = h.system_id) system_id,
       (select t.res_label name
          from [email protected] 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 [email protected] t
         where t.id = h.system_id and rownum<2) system_id,
       (select t.res_label name
          from [email protected] 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

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