CODEWITHSUNDEEP
oracle-database
Aqib Javed
Aqib Javed

Reputation: 914

ORA-01427: single-row subquery returns more than one row in oracle

I am facing an issue while fetching records from Oracle db. ORA-01427: single-row subquery returns more than one row. Here is my Query. I want to get a column value from the first query and pass that value to the next query. The code works fine for a small date range but with the range of 1 month it throwing an error. Any help would be appreciated.

  SELECT DISTINCT div.id,
    div.name div,
    sa.name sales_area,
    stations.name station,
    stations.station_type,
    stations.outlet_code,
    stations.id AS sid,
    mqi.mqtu_inspection_id,
    mqi.inspection_date,
    mqi.inspection_time,
    shortage_dtl2.prods AS no_of_short,
    shortage_dtl.shortage,
    prod_failure_dtl.prods,
    prod_failure_dtl.quality_failure
  FROM quantity_test_values q,
    products p,
    mqtu_inspections mqi,
    stations,
    salesarea_station ss,
    sales_areas sa,
    divisions div,
    (SELECT short_prod_dtl.insp_id,
      listagg(short_prod_dtl.shortage,', ') within GROUP(
    ORDER BY short_prod_dtl.INSP_ID, short_prod_dtl.prod_name) shortage
    FROM
      (SELECT dtl.insp_id,
        dtl.name prod_name,
        CASE
          WHEN SUM(tank_dry_count) > 0
          THEN 'N/A'
          WHEN SUM(dus_count_short) > 0
          THEN TO_CHAR(MIN(shortage_count_short))
          WHEN SUM(dus_count_Excess) >0
          THEN 'Excess'
          WHEN SUM(dus_count_accurate) > 0
          THEN 'Accurate'
          ELSE 'N/A'
        END shortage
      FROM
        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,
          CASE
            WHEN qtest.status = 'Short'
            THEN 1
            ELSE 0
          END dus_count_short,
          CASE
            WHEN qtest.status = 'Short'
            THEN qtest.deviation
            ELSE 0
          END shortage_count_short,
          CASE
            WHEN qtest.status = 'Accurate'
            THEN 1
            ELSE 0
          END dus_count_accurate,
          CASE
            WHEN qtest.status = 'Excess'
            THEN 1
            ELSE 0
          END dus_count_Excess
        FROM mqtu_inspections mqidtl,
          quantity_test_values qtest,
          products p,
          stations,
          salesarea_station ss,
          sales_areas sa,
          divisions div
        WHERE p.id                   =qtest.product_id
        AND mqidtl.mqtu_inspection_id=qtest.mqtu_inspection_id
        AND mqidtl.station_id        =stations.id
        AND stations.id              = ss.station_id
        AND sa.id                    = ss.salesarea_id
        AND div.id                   = sa.division_id
        AND mqidtl.inspection_date BETWEEN '01-Nov-2020' AND '30-Nov-2020'
        ) dtl
      GROUP BY dtl.insp_id,
        dtl.name
      ) short_prod_dtl
    GROUP BY short_prod_dtl.insp_id
    ) shortage_dtl,
    (SELECT short_prod_dtl.insp_id,
      listagg(short_prod_dtl.no_of_dus_short,', ') within GROUP(
    ORDER BY short_prod_dtl.INSP_ID, short_prod_dtl.prod_name) prods
    FROM
      (SELECT dtl.insp_id,
        dtl.name prod_name,
        CASE
          WHEN SUM(tank_dry_count) > 0
          THEN 'N/A'
          WHEN SUM(dus_count_short) > 0
          THEN TO_CHAR(SUM(dus_count_short))
          WHEN SUM(dus_count_Excess) >0
          THEN 'Excess'
          WHEN SUM(dus_count_accurate) > 0
          THEN 'Accurate'
          ELSE 'N/A'
        END no_of_dus_short
      FROM
        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,
          CASE
            WHEN qtest.status = 'Short'
            THEN 1
            ELSE 0
          END dus_count_short,
          CASE
            WHEN qtest.status = 'Short'
            THEN qtest.deviation
            ELSE 0
          END shortage_count_short,
          CASE
            WHEN qtest.status = 'Accurate'
            THEN 1
            ELSE 0
          END dus_count_accurate,
          CASE
            WHEN qtest.status = 'Excess'
            THEN 1
            ELSE 0
          END dus_count_Excess
        FROM mqtu_inspections mqidtl,
          quantity_test_values qtest,
          products p,
          stations,
          salesarea_station ss,
          sales_areas sa,
          divisions div
        WHERE p.id                   =qtest.product_id
        AND mqidtl.mqtu_inspection_id=qtest.mqtu_inspection_id
        AND mqidtl.station_id        =stations.id
        AND stations.id              = ss.station_id
        AND sa.id                    = ss.salesarea_id
        AND div.id                   = sa.division_id
        AND mqidtl.inspection_date BETWEEN '01-Nov-2020' AND '30-Nov-2020'
        ) dtl
      GROUP BY dtl.insp_id,
        dtl.name
      ) short_prod_dtl
    GROUP BY short_prod_dtl.insp_id
    )shortage_dtl2,
    (SELECT prd_dtl.insp_id,
      listagg(prd_dtl.prod_name,', ') within GROUP(
    ORDER BY PRD_DTL.INSP_ID, prd_dtl.prod_name) prods,
      listagg(prd_dtl.decision,', ') within GROUP(
    ORDER BY PRD_DTL.INSP_ID, prd_dtl.prod_name) quality_failure
    FROM
      (SELECT DISTINCT mqidtl.mqtu_inspection_id insp_id,
        prods.name prod_name,
        qtest.decision,
        mqidtl.mqtu_inspection_id
      FROM mqtu_inspections mqidtl,
        quality_test_values qtest,
        products prods,
        stations,
        salesarea_station ss,
        sales_areas sa,
        divisions div
      WHERE 1                 =1
      AND qtest.inspection_id = mqidtl.mqtu_inspection_id
      AND qtest.product_id    = prods.id
      AND mqidtl.station_id   =stations.id
      AND stations.id         = ss.station_id
      AND sa.id               = ss.salesarea_id
      AND div.id              = sa.division_id
      AND mqidtl.inspection_date BETWEEN '01-Nov-2020' AND '30-Nov-2020'
      )prd_dtl
    GROUP BY prd_dtl.insp_id
    ) prod_failure_dtl
  WHERE 1                    =1
  AND mqi.mqtu_inspection_id =q.mqtu_inspection_id
  AND mqi.station_id         =stations.id
  AND stations.id            = ss.station_id
  AND sa.id                  = ss.salesarea_id
  AND div.id                 = sa.division_id
  AND p.id                   =q.product_id
  AND mqi.mqtu_inspection_id = shortage_dtl.insp_id
  AND mqi.mqtu_inspection_id = shortage_dtl2.insp_id
  AND mqi.mqtu_inspection_id =prod_failure_dtl.insp_id
  AND ss.is_deleted          = 0
  AND mqi.inspection_date BETWEEN '01-Nov-2020' AND '30-Nov-2020'
  AND mqi.is_deleted=0
  ORDER BY div.id

Upvotes: 1

Views: 7250

Answers (1)

Dario
Dario

Reputation: 470

The error message is providing you a HINT for your research "ORA-01427: single-row subquery returns more than one row"

It means that you need to find a query in the SELECT clause returning more than one row. There are other possibilities but in your case, I've identified 2 :

        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,
          
      FROM
        (SELECT mqidtl.mqtu_inspection_id insp_id,
          p.name,
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count,

So you need to see why this query is returning more than one row.

SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = mqidtl.mqtu_inspection_id
              AND qlty.product_id      = p.id) = 'Tank dry'

Use the following query

SELECT qlty.product_id, qlty.inspection_id, count(1), count(distinct qlty.decision)
FROM quality_test_values qlty
GROUP BY qlty.inspection_id , qlty.product_id
HAVING count(distinct qlty.decision) > 1

Hope that this can help you

I don't know your data, so here a testcase


create table quality_test_values
( product_id integer, inspection_id integer, decision varchar2(30))

insert into quality_test_values values (1,1,'Tank dry');
insert into quality_test_values values (1,1,'Other Decision');

SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = 1
              AND qlty.product_id      = 1;

DECISION       
-------------- 
Tank dry       
Other Decision 

SELECT qlty.product_id, qlty.inspection_id, count(1), count(distinct qlty.decision)
FROM quality_test_values qlty
GROUP BY qlty.inspection_id , qlty.product_id
HAVING count(distinct qlty.decision) > 1

PRODUCT_ID INSPECTION_ID COUNT(1) COUNT(DISTINCTQLTY.DECISION) 
---------- ------------- -------- ---------------------------- 
         1             1        2                            2 


SELECT
          CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = W.inspection_id
              AND qlty.product_id      = W.product_id) = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count
FROM quality_test_values W

ORA-01427: single-row subquery returns more than one row

Proposed solution, ADD a where clause


        CASE
            WHEN (SELECT DISTINCT qlty.decision
              FROM quality_test_values qlty
              WHERE qlty.inspection_id = W.inspection_id
              AND qlty.product_id      = W.product_id
              AND qlty.decision = 'Tank dry') = 'Tank dry'
            THEN 1
            ELSE 0
          END tank_dry_count

Upvotes: 1

Related Questions