ORA-01427: single-row subquery returns more than one row
作者: moonsoft(http://moonsoft.itpub.net)发表于: 2007.01.18 11:57
分类: Oracle基础/数据仓库/BI , 财务
出处: http://moonsoft.itpub.net/post/15182/252306
---------------------------------------------------------------
ORA-01427: single-row subquery returns more than one row
我用了rowid 来获得数据,并且用Implicit Cursors(select into )来获取数据,出现返回了多rows.这个错误解决方法:
--错误,出现ORA-01427
SELECT SUBSTR (h.period_name, 1, 2), p.adjustment_period_flag
INTO v_month_b, v_month_a
FROM gl_je_headers h, gl_periods p, gl_sets_of_books b
WHERE h.ROWID = gl_je_headers_num_pkg.t_je (i)
and h.set_of_books_id = b.set_of_books_id
AND h.period_name = p.period_name
AND b.period_set_name = p.period_set_name;
--解决方法
SELECT h.je_header_id
INTO v_je_header_id
FROM gl_je_headers h
WHERE rowid = gl_je_headers_num_pkg.t_je (i);
SELECT SUBSTR (h.period_name, 1, 2), p.adjustment_period_flag
INTO v_month_b, v_month_a
FROM gl_je_headers h, gl_periods p, gl_sets_of_books b
WHERE h.je_header_id = v_je_header_id
AND h.set_of_books_id = b.set_of_books_id
AND h.period_name = p.period_name
AND b.period_set_name = p.period_set_name;
估计和rowid 有关.还不是很了解ROWID 的原理
