Single-Row Subquery Returns More Than One Row - Looking For Explination/Solution

Question

Long post here... Brace yourself...

I had created the following 4 queries that were ran daily in sequential order. These were created so my company could be able to scan tooling in and out of service rather than manually enter the dates. When shown out, scheduling cannot schedule jobs that use that tool. These were functioning appropriately and had not had any issues as long as one field was left null (SERVICE_DATE_IN):

Query 1: Tooling scanned out

UPDATE PMEQMT P
   SET SERVICE_DATE_OUT = (SELECT MAX(TL.TRANS_DATE)
                        FROM TRANSLOG TL
                        WHERE P.EQNO = TL.ITEMNO AND
                              TL.LOC_DESC = 'E-OUT OF SERVICE' AND
                              TL.TRANS_DATE >= SYSDATE - 1 AND
                              TL.TRANS_IN_OUT = 'IN'
                       )
WHERE P.CLASS = 'TL' AND
      P.SERVICE_DATE_OUT IS NULL

Query 2: Tooling scanned in

UPDATE PMEQMT P
   SET SERVICE_DATE_IN = (SELECT MAX(TL.TRANS_DATE)
                        FROM TRANSLOG TL
                        WHERE P.EQNO = TL.ITEMNO AND 
                              TL.LOC_DESC = 'E-IN SERVICE' AND
                              TL.TRANS_DATE >= SYSDATE - 1 AND
                              TL.TRANS_IN_OUT = 'IN'
                       )
WHERE P.CLASS = 'TL' AND
      P.SERVICE_DATE_IN IS NULL

Query 3: Wipe service date in in case it is not cleared

UPDATE PMEQMT
SET SERVICE_DATE_IN = NULL
WHERE SERVICE_DATE_OUT IS NULL AND
  SERVICE_DATE_IN IS NOT NULL AND
  CLASS = 'TL'

Query 4: Tooling is back, null both out/in dates

UPDATE PMEQMT
SET SERVICE_DATE_OUT = NULL,
SERVICE_DATE_IN = NULL 
WHERE SERVICE_DATE_OUT IS NOT NULL AND
  SERVICE_DATE_IN IS NOT NULL AND
  CLASS = 'TL'

Engineering has requested to be able to manually enter a SERVICE_DATE_IN date. So I came up with the following queries:

Query 1: Same as query 1 above

Query 2: This is my problem query. Currently I am receiving error:

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

This query worked when I was testing and only moving one P.EQNO (items) to the TL.LOC_DESC (locations). I than began testing moving other P.EQNO (items) to different locations and then started receiving the error.

Is anyone able to explain what is occurring here and if you're feeling extra nice, help me modify the query to solve the error?

UPDATE PMEQMT P
   SET SERVICE_DATE_IN =
   CASE 
   WHEN SERVICE_DATE_IN IS NULL THEN (SELECT MAX(TL.TRANS_DATE)
                        FROM TRANSLOG TL
                        WHERE P.EQNO = TL.ITEMNO AND 
                              TL.LOC_DESC = 'E-IN SERVICE' AND
                              TL.TRANS_DATE >= SYSDATE - 1 AND
                              TL.TRANS_IN_OUT = 'IN'
                       )
   WHEN (TRUNC(SERVICE_DATE_IN)) <= (TRUNC(SYSDATE)) THEN (SELECT ((TRUNC(SYSDATE))+1)
                        FROM TRANSLOG TL
                        WHERE P.EQNO = TL.ITEMNO AND
                        TL.LOC_DESC = 'E-OUT OF SERVICE'
                       )
   WHEN (TRUNC(SERVICE_DATE_IN)) > (TRUNC(SYSDATE)) THEN (SELECT SERVICE_DATE_IN 
                        FROM TRANSLOG TL
                        WHERE P.EQNO = TL.ITEMNO AND
                        TL.LOC_DESC = 'E-OUT OF SERVICE'
                       )
   END
WHERE CLASS = 'TL'

Query 3: No issues

UPDATE PMEQMT P 
SET P.SERVICE_DATE_OUT = NULL, P.SERVICE_DATE_IN = NULL 
WHERE EXISTS (SELECT 1 FROM TRANSLOG TL WHERE TL.ITEMNO = P.EQNO AND TL.LOC_DESC = 'E-IN SERVICE')

If you see any issues with my code or have a way to better it I am open ears!

--------------------EDIT-------------------------

Thanks to @GMB I have a correct working query. I am now running into the issue where I have two of the same WHEN conditions.

UPDATE PMEQMT P 
SET SERVICE_DATE_IN = CASE 
WHEN SERVICE_DATE_IN IS NULL 
    THEN (
        SELECT MAX(TL.TRANS_DATE)
        FROM TRANSLOG TL
        WHERE 
            P.EQNO = TL.ITEMNO
            AND TL.LOC_DESC = 'E-IN SERVICE' 
            AND TL.TRANS_DATE >= SYSDATE - 1 
            AND TL.TRANS_IN_OUT = 'IN'
    )
WHEN 
    TRUNC(SERVICE_DATE_IN) <= TRUNC(SYSDATE) 
    AND EXISTS (        
        SELECT 1
        FROM TRANSLOG TL
        WHERE 
            P.EQNO = TL.ITEMNO 
            AND TL.LOC_DESC = 'E-OUT OF SERVICE'
    )
    THEN TRUNC(SYSDATE) +1        
WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE) 
    THEN (
        SELECT MAX(SERVICE_DATE_IN)
        FROM TRANSLOG TL
        WHERE 
            P.EQNO = TL.ITEMNO
            AND TL.LOC_DESC = 'E-OUT OF SERVICE'
    )
WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE)
    THEN (
          SELECT MAX(TL.TRANS_DATE)
          FROM TRANSLOG TL
          WHERE 
            P.EQNO = TL.ITEMNO
            AND TL.LOC_DESC = 'E-IN SERVICE' 
            AND TL.TRANS_DATE >= SYSDATE - 1 
            AND TL.TRANS_IN_OUT = 'IN'
    )
  END
WHERE CLASS = 'TL'

The record I was testing kept the MAX(SERVICE_DATE_IN) data.What are my options here? I was going to do a nested CASE but I would run into the same issue. I could do a separate query that runs after this one that looks purely at the last set of conditions. I'm just wondering if it is possible to do within the same query. It's really dependent on the TL.LOC_DESC value.

GMB GMB · Accepted Answer · 2019-04-03T23:25:13

You need some kind of logic to limit the number of records returned by the subuquery to just one.

In the first subquery (that was part of your first version the query), this is provided by using aggregation without a GROUP BY clause:

SET SERVICE_DATE_IN = (
    SELECT MAX(TL.TRANS_DATE)
    FROM TRANSLOG TL
    WHERE 
        P.EQNO = TL.ITEMNO
        AND TL.LOC_DESC = 'E-IN SERVICE'
        AND TL.TRANS_DATE >= SYSDATE - 1
        AND TL.TRANS_IN_OUT = 'IN'
)

The second subquery, where the returned value is basically TRUNC(SYSDATE) +1, could be moved to a NOT EXISTS condition within the relevant WHEN part of the CASE statement.

The third subquery can be fixed using aggregation.

Consider:

UPDATE PMEQMT P 
SET SERVICE_DATE_IN = CASE 
    WHEN SERVICE_DATE_IN IS NULL 
        THEN (
            SELECT MAX(TL.TRANS_DATE)
            FROM TRANSLOG TL
            WHERE 
                P.EQNO = TL.ITEMNO
                AND TL.LOC_DESC = 'E-IN SERVICE' 
                AND TL.TRANS_DATE >= SYSDATE - 1 
                AND TL.TRANS_IN_OUT = 'IN'
        )
    WHEN 
        TRUNC(SERVICE_DATE_IN) <= TRUNC(SYSDATE) 
        AND EXISTS (        
            SELECT 1
            FROM TRANSLOG TL
            WHERE 
                P.EQNO = TL.ITEMNO 
                AND TL.LOC_DESC = 'E-OUT OF SERVICE'
        )
        THEN TRUNC(SYSDATE) +1        
    WHEN TRUNC(SERVICE_DATE_IN) > TRUNC(SYSDATE 
        THEN (
            SELECT MAX(SERVICE_DATE_IN)
            FROM TRANSLOG TL
            WHERE 
                P.EQNO = TL.ITEMNO
                AND TL.LOC_DESC = 'E-OUT OF SERVICE'
        )
   END
WHERE CLASS = 'TL'

Single-Row Subquery Returns More Than One Row - Looking For Explination/Solution

2 Answers