Question

oracle查询报这个错误：single-row subquery returns more than one row，求高手解答

CREATE VIEW CUX_EHR_POS_STRUCTURE_V AS SELECT o.codeitemid EPK,k.K011J OPK,'苏轨人力层级' STRUCTURE_NAME,
(select K011H from K01 kk where kk.E01A1=k.K0118) PARENT_POSITION_CODE,(select og.codeitemdesc from organization og,K01 kk where og.corCode=kk.K011H and kk.E01A1=k.K0118) PARENT_POSITION_NAME,(select cd.codeitemdesc from codeitem cd,K01 kk where cd.codesetid = 'YW' and cd.codeitemid=kk.K011K and kk.E01A1=k.K0118) PARENT_POSITION_CLASS_CODE,
(select cd.codeitemdesc from codeitem cd,K01 kk where cd.codesetid = 'ZN' and cd.codeitemid=kk.K011L and kk.E01A1=k.K0118) PARENT_POSITION_CLASS_NAME,k.K011H CHILD_POSITION_CODE,(select codeitemdesc from organization og where og.corCode=k.K011H) CHILD_POSITION_NAME,(select codeitemdesc from codeitem where codesetid = 'YW' and codeitemid=k.K011K) CHILD_POSITION_CLASS_CODE,(select codeitemdesc from codeitem where codesetid = 'ZN' and codeitemid=k.K011L) CHILD_POSITION_CLASS_NAME,
CASE WHEN (k.ModTime is null or k.ModTime='') then k.CreateTime else k.ModTime end LAST_UPDATE_DATE
FROM organization o,K01 k where o.codesetid='@K' and o.codeitemid=k.E01A1

Answer 1

你好，这个错误一般是由于你select中嵌套的select子查询返回不止一条数据导致的，即你的sql
/* Formatted on 2013-11-21 16:50:43 (QP5 v5.163.1008.3004) */
CREATE VIEW CUX_EHR_POS_STRUCTURE_V
AS
SELECT o.codeitemid EPK,
k.K011J OPK,
'苏轨人力层级' STRUCTURE_NAME,
(SELECT K011H
FROM K01 kk
WHERE kk.E01A1 = k.K0118)
PARENT_POSITION_CODE,
(SELECT og.codeitemdesc
FROM organization og, K01 kk
WHERE og.corCode = kk.K011H AND kk.E01A1 = k.K0118)
PARENT_POSITION_NAME,
(SELECT cd.codeitemdesc
FROM codeitem cd, K01 kk
WHERE cd.codesetid = 'YW'
AND cd.codeitemid = kk.K011K
AND kk.E01A1 = k.K0118)
PARENT_POSITION_CLASS_CODE,
(SELECT cd.codeitemdesc
FROM codeitem cd, K01 kk
WHERE cd.codesetid = 'ZN'
AND cd.codeitemid = kk.K011L
AND kk.E01A1 = k.K0118)
PARENT_POSITION_CLASS_NAME,
k.K011H CHILD_POSITION_CODE,
(SELECT codeitemdesc
FROM organization og
WHERE og.corCode = k.K011H)
CHILD_POSITION_NAME,
(SELECT codeitemdesc
FROM codeitem
WHERE codesetid = 'YW' AND codeitemid = k.K011K)
CHILD_POSITION_CLASS_CODE,
(SELECT codeitemdesc
FROM codeitem
WHERE codesetid = 'ZN' AND codeitemid = k.K011L)
CHILD_POSITION_CLASS_NAME,
CASE
WHEN (k.ModTime IS NULL OR k.ModTime = '') THEN k.CreateTime
ELSE k.ModTime
END
LAST_UPDATE_DATE
FROM organization o, K01 k
WHERE o.codesetid = '@K' AND o.codeitemid = k.E01A1；

可能类似 (SELECT K011H
FROM K01 kk
WHERE kk.E01A1 = k.K0118)
的子查询返回数据过多导致，你可以对你的这些子查询的数据做一下分析，或者你不想分析的话，直接都在子查询里面加上rownum=1就可以了，即
(SELECT K011H
FROM K01 kk
WHERE kk.E01A1 = k.K0118 and rownum = 1)

Answer 2

这个没看懂。

为了开发和维护的方便，Oracle定义了常见的异常，主要有以下几个：

1，DUP_VAL_ON_INDEX        异常码 ORA-00001        试图向唯一索引列插入重复值

2，INVALID_CURSOR        异常码 ORA-01001        试图进行非法游标操作

3，INVALID_NUMBER        异常码 ORA-01722        试图将字符串转化成数字

4，NO_DATA_FOUND        异常码 ORA-01403        SELECT INTO 语句中没有任何返回结果

5，TOO_MANY_ROWS        异常码 ORA-01422        SELECT INTO 语句中返回结果

6，ZERO_DIVIDE        异常码 ORA-01476        试图用0作为除数

7，CURSOR_ALREADY_OPEN        异常码 ORA-06511        试图打开一个已经打开的游标