CODEWITHSUNDEEP
sqlsubqueryoracle-sqldeveloper
Ritesh
Ritesh

Reputation: 91

single-row subquery returns more than one row. Query not working with main query

I hve to display several cell values into one cell. So I am using this query:

select LISTAGG(fc.DESCRIPTION,  ';'||chr(10))WITHIN GROUP (ORDER BY fc.SWITCH_NAME) AS DESCRIP from "ORS".SWITCH_OPERATIONS fc
       group by fc.SWITCH_NAME

It is working fine. But when I am merging this with my main(complete) query then I am getting the error as: Error code 1427, SQL state 21000: ORA-01427: single-row subquery returns more than one row

Here is my complete query:

SELECT
     TRACK_EVENT.LOCATION,
     TRACK_EVENT.ELEMENT_NAME,
     (select COUNT(*) from ORS.TRACK_EVENT b where (b.ELEMENT_NAME = sw.SWITCH_NAME)AND (b.ELEMENT_TYPE = 'SWITCH')AND (b.EVENT_TYPE = 'I')AND (b.ELEMENT_STATE = 'NORMAL' OR b.ELEMENT_STATE = 'REVERSE'))as COUNTER,
     (select COUNT(*) from ORS.SWITCH_OPERATIONS fc where TRACK_EVENT.ELEMENT_NAME = fc.SWITCH_NAME and fc.NO_CORRESPONDENCE = 1 )as FAIL_COUNT,
     (select MAX(cw.COMMAND_TIME) from ORS.SWITCH_OPERATIONS cw where ((TRACK_EVENT.ELEMENT_NAME = cw.SWITCH_NAME) and (cw.NO_CORRESPONDENCE = 1)) group by cw.SWITCH_NAME ) as FAILURE_DATE,
     (select LISTAGG(fc.DESCRIPTION,  ';'||chr(10))WITHIN GROUP (ORDER BY fc.SWITCH_NAME) AS DESCRIP from "ORS".SWITCH_OPERATIONS fc
       group by fc.SWITCH_NAME)

FROM
    ORS.SWITCH_OPERATIONS sw,
    ORS.TRACK_EVENT TRACK_EVENT
WHERE
    sw.SEQUENCE_ID = TRACK_EVENT.SEQUENCE_ID

Upvotes: 0

Views: 9740

Answers (2)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

Not only are subqueries in the SELECT list required to return exactly one row (or any time they're used for a singular comparison, like <, =, etc), but their use in that context tends to make the database execute them RBAR - Row-by-agonizing-row. That is, they're slower and consume more resources than they should.

Generally, unless the result set outside the subquery contains only a few rows, you want to construct subqueries as part of a table-reference. Ie, something like:

SELECT m.n, m.z, aliasForSomeTable.a, aliasForSomeTabe.bSum
FROM mainTable m
JOIN (SELECT a, SUM(b) AS bSum
      FROM someTable
      GROUP BY a) aliasForSomeTable
  ON aliasForSomeTable.a = m.a

This benefits you in other ways to - it's easier to get multiple columns out of the same table-reference, for example.

Assuming that LISTAGG(...) can be included with other aggregate functions, you can change your query to look like this:

SELECT Track_Event.location, Track_Event.element_name, 
       Counted_Events.counter,
       Failure.fail_count, Failure.failure_date, Failure.descrip
FROM ORS.Track_Event
JOIN ORS.Switch_Operations
  ON Switch_Operations.sequence_id = Track_Event.sequence_id
LEFT JOIN (SELECT element_name, COUNT(*) AS counter
           FROM ORS.Track_Event
           WHERE element_type = 'SWITCH'
                 AND event_type = 'I'
                 AND element_state IN ('NORMAL', 'REVERSE') 
           GROUP BY element_name) Counted_Events
       ON Counted_Events.element_name = Switch_Operations.swicth_name 
LEFT JOIN (SELECT switch_name,
                  COUNT(CASE WHEN no_correspondence = 1 THEN '1' END) AS fail_count,
                  MAX(CASE WHEN no_correspondence = 1 THEN command_time END) AS failure_date,
                  LISTAGG(description,  ';' || CHAR(10)) WITHIN GROUP (ORDER BY command_time) AS descrip
           FROM ORS.Switch_Operations
           GROUP BY switch_name) Failure
       ON Failure.switch_name = Track_Event.element_name

This query was written to (attempt to) preserve the semantics of your original query. I'm not completely sure that's what you actually need but without sample starting data and desired results, I have no way to tell how else to improve this. For instance, I'm a little suspicious of the need of Switch_Operations in the outer query, and the fact that LISTAGG(...) is run over row where no_correspondence <> 1. I did change the ordering of LISTAGG(...), because the original column would not have done anything (because the order way the same as the grouping), so would not have been a stable sort.

Upvotes: 6

ElmoVanKielmo
ElmoVanKielmo

Reputation: 11290

Single-row subquery returns more than one row.
This error message is self descriptive.
Returned field can't have multiple values and your subquery returns more than one row.
In your complete query you specify fields to be returned. The last field expects single value from the subquery but gets multiple rows instead.

I have no clue about the data you're working with but either you have to ensure that subquery returns only one row or you have to redesign the wrapping query (possibly using joins when appropriate).

Upvotes: 1

Related Questions