ORA-01427错误解决咨询：单行子查询返回多行问题

Let's break down exactly what's happening here: your three column-level subqueries (pstsc_before_ea, aap_before_ea, aap_after_ea) are meant to return one value per row in the main query, but for at least one row, they're spitting out multiple records. Oracle can't cram multiple values into a single column, hence the error.

Here are three actionable solutions tailored to your SQL, ordered by best practice:

1. Replace Scalar Subqueries with Joins (Recommended) #

Scalar subqueries can be inefficient and error-prone when dealing with multi-match scenarios. Rewriting them as LEFT JOIN with aggregated or filtered subqueries is cleaner and more performant.

Here's how to adjust your CREATE TABLE statement:

CREATE TABLE i86813_dt190429 as 
SELECT /*+ use_hash(RAP01 RAA02 RAP06) */ 
       DISTINCT 'I86813' AS audit_id, 
       rap01.plcy AS plcy, 
       rap01.stuscd, 
       raa02.enddt_t AS enddt, 
       rap01.j01_pt_line_cat_cd AS j01_pt_line_cat_cd, 
       rap01.j01_pt_cdb_part_id AS j01_pt_cdb_part_id, 
       rap01.j01_pt_state_cd AS j01_pt_state_cd, 
       rap06.sctype, 
       raa02.EACPRC, 
       pstsc_sub.pstsc AS pstsc_before_ea,
       aap_before_sub.aap AS aap_before_ea,
       aap_after_sub.aap AS aap_after_ea
FROM rap01
JOIN raa02 
  ON raa02.j46_pt_line_cat_cd = rap01.j01_pt_line_cat_cd 
  AND raa02.j46_pt_cdb_part_id = rap01.j01_pt_cdb_part_id 
  AND raa02.j46_pt_state_cd = rap01.j01_pt_state_cd 
  AND raa02.plcy = rap01.plcy 
  AND raa02.EACPRC = '25' 
  AND raa02.ahevnt = '0993' 
  AND raa02.sprodt_t BETWEEN '13-AUG-2018' and '14-APR-2019'
LEFT JOIN RAP06 
  ON RAP06.J42_PT_LINE_CAT_CD = RAP01.J01_PT_LINE_CAT_CD 
  AND RAP06.J42_PT_CDB_PART_ID = RAP01.J01_PT_CDB_PART_ID 
  AND RAP06.J42_PT_STATE_CD = RAP01.J01_PT_STATE_CD 
  AND RAP06.PLCY = RAP01.PLCY 
  AND RAP06.SCTYPE = '085' 
  AND RAA02.enddt_t BETWEEN RAP06.ENDDT_T AND (RAP06.DROPDT_T - 1)
-- Subquery for pstsc_before_ea (aggregated to ensure single row per plcy)
LEFT JOIN (
    SELECT rap06.plcy, MAX(rap06.pstsc) AS pstsc
    FROM rap06 
    JOIN raa02 ON rap06.plcy = raa02.plcy 
    WHERE raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )
    GROUP BY rap06.plcy
) pstsc_sub ON pstsc_sub.plcy = rap01.plcy
-- Subquery for aap_before_ea
LEFT JOIN (
    SELECT rap01.plcy, MAX(rap01.aap) AS aap
    FROM rap01 
    JOIN raa02 ON rap01.plcy = raa02.plcy 
    WHERE raa02.enddt_t - 1 = rap01.enddt_t
    GROUP BY rap01.plcy
) aap_before_sub ON aap_before_sub.plcy = rap01.plcy
-- Subquery for aap_after_ea
LEFT JOIN (
    SELECT rap01.plcy, MAX(rap01.aap) AS aap
    FROM rap01 
    JOIN raa02 ON rap01.plcy = raa02.plcy 
    WHERE raa02.enddt_t > rap01.enddt_t
    GROUP BY rap01.plcy
) aap_after_sub ON aap_after_sub.plcy = rap01.plcy
WHERE rap01.j01_pt_line_cat_cd = 'A' 
  AND rap01.co3 || rap01.line3 IN ( '065010', '010010', '027010', '021010', '386010', '065019', '010019', '027019', '021019', '386019' ) 
  AND RAP06.PLCY is NULL;

If you need to retrieve the latest value instead of just max/min, use a window function like ROW_NUMBER() in the subqueries:

-- Example for pstsc_before_ea using ROW_NUMBER()
LEFT JOIN (
    SELECT plcy, pstsc
    FROM (
        SELECT rap06.plcy, rap06.pstsc,
               ROW_NUMBER() OVER (PARTITION BY rap06.plcy ORDER BY rap06.enddt_t DESC) AS rn
        FROM rap06 
        JOIN raa02 ON rap06.plcy = raa02.plcy 
        WHERE raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )
    ) t
    WHERE rn = 1 -- Only keep the latest record per plcy
) pstsc_sub ON pstsc_sub.plcy = rap01.plcy

2. Convert to Correlated Subqueries #

If you prefer to keep the subquery structure, make them correlated so they only return results for the current row in the main query. This requires adding a link between the subquery and the main query's table (e.g., matching plcy):

First, alias the main rap01 table as main, then update each subquery:

CREATE TABLE i86813_dt190429 as 
SELECT /*+ use_hash(RAP01 RAA02 RAP06) */ 
       DISTINCT 'I86813' AS audit_id, 
       main.plcy AS plcy, 
       main.stuscd, 
       raa02.enddt_t AS enddt, 
       main.j01_pt_line_cat_cd AS j01_pt_line_cat_cd, 
       main.j01_pt_cdb_part_id AS j01_pt_cdb_part_id, 
       main.j01_pt_state_cd AS j01_pt_state_cd, 
       rap06.sctype, 
       raa02.EACPRC, 
       -- Correlated subquery for pstsc_before_ea
       (SELECT MAX(rap06.pstsc) 
        FROM rap06 
        JOIN raa02 ON rap06.plcy = raa02.plcy 
        WHERE raa02.plcy = main.plcy -- Link to main query's plcy
          AND raa02.enddt_t - 1 BETWEEN rap06.enddt_t AND ( rap06.dropdt_t - 1 )) AS pstsc_before_ea,
       -- Correlated subquery for aap_before_ea
       ( SELECT rap01.aap 
         FROM rap01 
         JOIN raa02 ON rap01.plcy = raa02.plcy 
         WHERE rap01.plcy = main.plcy -- Link to main query's plcy
           AND raa02.enddt_t - 1 = rap01.enddt_t ) AS aap_before_ea,
       -- Correlated subquery for aap_after_ea
       ( SELECT rap01.aap 
         FROM rap01 
         JOIN raa02 ON rap01.plcy = raa02.plcy 
         WHERE rap01.plcy = main.plcy -- Link to main query's plcy
           AND raa02.enddt_t > rap01.enddt_t ) AS aap_after_ea
FROM rap01 main -- Alias main table
JOIN raa02 
  ON raa02.j46_pt_line_cat_cd = main.j01_pt_line_cat_cd 
  AND raa02.j46_pt_cdb_part_id = main.j01_pt_cdb_part_id 
  AND raa02.j46_pt_state_cd = main.j01_pt_state_cd 
  AND raa02.plcy = main.plcy 
  AND raa02.EACPRC = '25' 
  AND raa02.ahevnt = '0993' 
  AND raa02.sprodt_t BETWEEN '13-AUG-2018' and '14-APR-2019'
LEFT JOIN RAP06 
  ON RAP06.J42_PT_LINE_CAT_CD = main.j01_pt_line_cat_cd 
  AND RAP06.J42_PT_CDB_PART_ID = main.j01_pt_cdb_part_id 
  AND RAP06.J42_PT_STATE_CD = main.j01_pt_state_cd 
  AND RAP06.PLCY = main.plcy 
  AND RAP06.SCTYPE = '085' 
  AND RAA02.enddt_t BETWEEN RAP06.ENDDT_T AND (RAP06.DROPDT_T - 1)
WHERE main.j01_pt_line_cat_cd = 'A' 
  AND main.co3 || main.line3 IN ( '065010', '010010', '027010', '021010', '386010', '065019', '010019', '027019', '021019', '386019' ) 
  AND RAP06.PLCY is NULL;

Note: I added MAX() to the first subquery because even with correlation, there might still be multiple matches per plcy. Adjust based on your business needs.

3. Validate Data and Business Logic #

Before applying fixes, it's worth checking why the subqueries are returning multiple rows:

• Run the subqueries independently with sample plcy values from your main query to spot duplicate records.
• Confirm if your business logic expects multiple values (if so, use LISTAGG() to concatenate them into a single string instead of returning a single value).
• Check for missing filters in the subqueries (e.g., additional date ranges or status codes that would narrow results to one row).

内容的提问来源于stack exchange，提问作者Kaushal Talniya