Question

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

SELECT BO.VESSEL,
BO.VOY,
BO.ETD,
BO.PORTLOAD,
BO.PORTVIA,
BO.PORTDEST,
(SELECT BKCTN.PNUM
FROM BKCTN
WHERE CTNSIZE = '20' AND BO.BOID = BKCTN.BOID(+) ) AS "20",
(SELECT BKCTN.PNUM
FROM BKCTN
WHERE CTNSIZE = '40' AND BO.BOID = BKCTN.BOID(+) ) AS "40",
BO.GWET
FROM BO

Answer 1

(SELECT BKCTN.PNUM
FROM BKCTN
WHERE CTNSIZE = '20' AND BO.BOID = BKCTN.BOID(+) ) AS "20",
(SELECT BKCTN.PNUM
FROM BKCTN
WHERE CTNSIZE = '40' AND BO.BOID = BKCTN.BOID(+) ) AS "40",
上面这两个语句里面的查询结果是多行了。

Answer 2

Try this, it should be much more effective than yours

SELECT BO.VESSEL,
BO.VOY,
BO.ETD,
BO.PORTLOAD,
BO.PORTVIA,
BO.PORTDEST,
CASE WHEN CTNSIZE = 20 then BKCTN.PNUM ELSE NULL END,
CASE WHEN CTNSIZE = 40 then BKCTN.PNUM ELSE NULL END,
BO.GWET
FROM BO LEFT JOIN BKCTN ON BO.BOID = BKCTN.BOID

Answer 3

说明你的子查询返回的数据不止一条数据，而是多条数据库，
先检查下你的子查询结果是几条数据