ORA-01427: single-row subquery returns more than one row - I get this error even though I use SELECT DISTINCT

1
votes

I am trying to make a query that selects all the drivers that were fined in the same place where another driver was fined. 

SELECT DISTINCT(c.IdSofer), s.NumeSofer
FROM Soferi s
INNER JOIN contraventii c ON c.IdSofer= s.IdSofer
HAVING (SELECT DISTINCT IdLocContr FROM Contraventii c 
INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
WHERE s.NumeSofer='Maneta Gheorghe' AND l.DenLoc='Pocreaca' AND l.Jud='IS' GROUP BY IdLocContr) = c.IdLocContr
GROUP BY s.NumeSofer, c.IdLocContr, c.IdSofer;

This is what I tried but I get the error message

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

If I run only only the SELECT statement between parentheses it works fine. It shows the id of cities (two cities) where this driver was fined. But if I try to run the code as I wrote here I get this error.

enter image description here

EDIT

This is the SQL from my comment...

SELECT DISTINCT(c.IdSofer)
      ,s.NumeSofer
  FROM Soferi s INNER JOIN contraventii c
    ON c.IdSofer= s.IdSofer
 GROUP BY s.NumeSofer
         ,c.IdLocContr
         ,c.IdSofer
HAVING (SELECT DISTINCT IdLocContr
          FROM Contraventii c INNER JOIN Soferi s
            ON c.IdSofer=s.IdSofer INNER JOIN Localitati l
                                           ON s.IdLocSofer=l.IdLoc
         WHERE s.NumeSofer = 'Maneta Gheorghe'
           AND l.DenLoc = 'Pocreaca'
           AND l.Jud = 'IS'
      GROUP BY IdLocContr) = c.IdLocContr
3
sqloracle
share sample data and expected output – Zaynul Abadin Tuhin
In a SQL query, the HAVING clause must follow a GROUP BY. In the SQL you posted, HAVING appears before GROUP BY. – Abra
I have put a photo with the extras from DB where are shown only the fines from the cities where the driver was finned. The output has to be the driver name "s.NumeSofer" – BeginerUser
That should be a correlated subquery in the where clause I suspect. Definitely not a typical having. – shawnt00
Probably a good idea to learn SQL syntax before writing complicated query. – Eric

3 Answers

2
votes
WHERE c.IdLocContr IN (
    SELECT DISTINCT IdLocContr
    FROM Contraventii c 
        INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
        INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
    WHERE s.NumeSofer = 'Maneta Gheorghe' AND l.DenLoc = 'Pocreaca' AND l.Jud = 'IS'
)

Just a few notes for your benefit.

The distinct isn't really necessary inside an in subquery although I left it there. Also you were using both distinct and group by which were redundant for that query. Having comes into play when you want to filter based on group aggregates.

0
votes

It's too long to put in comment, so I put it here. Debugging is part of programmer's job. Have you checked whether the query below returns one row or not? If not, then that's what you have to fix.

SELECT DISTINCT IdLocContr 
FROM Contraventii c 
INNER JOIN Soferi s ON c.IdSofer=s.IdSofer
INNER JOIN Localitati l ON s.IdLocSofer=l.IdLoc
WHERE s.NumeSofer='Maneta Gheorghe' AND l.DenLoc='Pocreaca' AND l.Jud='IS' 
GROUP BY IdLocContr

From the look at it, I doubt it will only return 1 row.

0
votes

your having clause should return one value:

SELECT DISTINCT IdLocContr
          FROM Contraventii c INNER JOIN Soferi s
            ON c.IdSofer=s.IdSofer INNER JOIN Localitati l
                                           ON s.IdLocSofer=l.IdLoc
         WHERE s.NumeSofer = 'Maneta Gheorghe'
           AND l.DenLoc = 'Pocreaca'
           AND l.Jud = 'IS'
           AND rownum<=1
      GROUP BY IdLocContr