Fix SQL subquery return multiple row

Question

I am running this query with oracle but I get the famous oracle error ORA-01427: single-row subquery returns more than one row

Select
  DISTINCT s1.cityArrival,
  s2.cityDeparture,
  s2.sectionStartDate,
  userFirstName
FROM
  sections s1,
  sections s2,
  trajectory t,
  userInfo
WHERE
  s1.trajectId = s2.trajectId
  AND t.trajectId = s1.trajectId
  AND (
    s1.cityArrival = 'Paris'
    AND s2.cityDeparture = 'Rochelle'
  )
  AND driverMail = mailUser
  AND (
    Select
      availableSeats
    FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
  ) not IN (0)

I want to select the trajectories given a date and a city of arrival and city of departure having for each section an availableSeats > 0. The problem is that I cannot find a way to do this without for loop in sql and it becomes complicated.

SectionId is just the index of the enroute city in a proposed trajectory, that is why I can use it to get all the chuncks between two given destination.

NB: I made the design of the DB, if you have remarks on the design don't hesitate to tell me, Thanks in advance

The only part that can cause that error is the subquery compared to NOT IN (0) (why not use <>0). Here the sections has a primary key of tracjectoryid and sectionid but it is queries with an inequality (<=) operator. This can clearly return more than one row (and it did). Can you tell what need to happen when that occurs? — tinazmu
– tinazmu, Commented Apr 10, 2022 at 11:25
I get the same error Error in query: ORA-01427: single-row subquery returns more than one row — rudeus123
– rudeus123, Commented Apr 10, 2022 at 11:30

Serg · Accepted Answer · 2022-04-10 11:34:31Z

1

You can reverse the IN operands provided exactly one value 0 is to be checked.

..
  AND 0 not IN (
    Select
      availableSeats
    FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
  )

answered Apr 10, 2022 at 11:34

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

EbixG · Accepted Answer · 2022-04-10 11:38:57Z

1

The error was caused by the subquery before the IN clause. The first part returns a result set rather than an expression. Here is the syntax for using IN clause: https://docs.oracle.com/cd/B19306_01/server.102/b14200/conditions013.htm

On the other hand, you can update the last subquery in the WHERE clause into

...
AND EXISTS (SELECT 1 
      FROM
      sections s3
    WHERE
      s3.trajectId = s1.trajectId
      AND s2.sectionId <= s3.sectionId
      AND s3.sectionId <= s1.sectionId
      AND s3.availableSeats > 0
)

answered Apr 10, 2022 at 11:38

EbixG

661 silver badge5 bronze badges

Collectives™ on Stack Overflow

Fix SQL subquery return multiple row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related