Oracle ORA-01427 : Single-rpw subquery returns more than on row

當執行Orale PL/SQL 查詢出現 ORA-01427 錯誤

  SELECT id, SUM (man_min)
    FROM workers_reported
   WHERE id = (SELECT id
                          FROM project
                         WHERE type = 'MO' AND status = 'CLOSE')
GROUP BY id, man_min

因為會產生一筆資料對多筆情況，此時Oracle回應ORA-01427 的錯誤訊息。

處理方式，Subquery 部份可以改採用 ALL/ANY/IN/NOT IN 方式處理，所以程式改為IN(或其它符合條件)即可。

  SELECT project_id, SUM (man_min)
    FROM ps_workers_reported
   WHERE project_id IN (SELECT project_id
                          FROM pj_project
                         WHERE pjt_type = 'MO' AND hold_status = 'CLOSE')
GROUP BY project_id, man_min

參考資源