Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14

    Unanswered: newbie: SUBQUERY RETURNS MORE THAN ONE ROW

    Hi,

    I have a table which looks like this:

    MACHINE ALARMNUM DETECTIONTIME EVENTDETAIL
    ---------- ---------- --------------- --------------
    SERVER1 000002 09:15:05 Started
    SERVER1 000002 09:15:06 Acknowledged
    SERVER1 000002 09:15:07 Done
    SERVER2 000003 10:50:01 Started
    SERVER2 000003 10:50:02 Acknowledged
    SERVER2 000003 10:50:03 Done

    I need to create a query that would result to this:

    MACHINE ALARMNUM STARTED ACKNOWLEDGED DONE
    ------- --------- --------- ------------- ------------
    SERVER1 000002 09:15:05 09:15:06 09:15:07
    SERVER2 000003 10:50:01 10:50:02 10:50:03

    The query I worked up with so far is:

    SELECT
    A.MACHINE,
    A.ALARMNUM,
    (SELECT B.DETECTIONTIME FROM MY_TABLE B WHERE A.ALARMNUM=B.ALARMNUM AND A.EVENTDETAIL LIKE 'Started%') STARTED,
    (SELECT B.DETECTIONTIME FROM MY_TABLE B WHERE A.ALARMNUM=B.ALARMNUM AND A.EVENTDETAIL LIKE 'Acknowledged%') ACKNOWLEDGED,
    (SELECT B.DETECTIONTIME FROM MY_TABLE B WHERE A.ALARMNUM=B.ALARMNUM AND A.EVENTDETAIL like 'Done%') DONE
    FROM MY_TABLE A ;

    But return the following error message:

    ERROR at line 4:
    ORA-01427: single-row subquery returns more than one row

    Appreciate any help.

    Thanks in advance!
    Ricky

  2. #2
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14
    Here is another version of the table and my desired result:

    MACHINE....ALARMNUM...DETECTIONTIME...EVENTDETAIL
    ----------.....----------.......---------------........--------------
    SERVER1....000002.........09:15:05.............Sta rted
    SERVER1....000002.........09:15:06.............Ack nowledged
    SERVER1....000002.........09:15:07.............Don e.
    SERVER2....000003.........10:50:01.............Sta rted
    SERVER2....000003.........10:50:02.............Ack nowledged
    SERVER2....000003.........10:50:03.............Don e


    MACHINE...ALARMNUM..STARTED....ACKNOWLEDGED...DONE
    -----------...---------......---------.......-------------............------------
    SERVER1...000002.......09:15:05.....09:15:06...... .........09:15:07
    SERVER2...000003.......10:50:01.....10:50:02...... .........10:50:03

    I hope this makes it clearer.

    Ricky (again)

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by spricks
    Here is another version of the table and my desired result:

    MACHINE....ALARMNUM...DETECTIONTIME...EVENTDETAIL
    ----------.....----------.......---------------........--------------
    SERVER1....000002.........09:15:05.............Sta rted
    SERVER1....000002.........09:15:06.............Ack nowledged
    SERVER1....000002.........09:15:07.............Don e.
    SERVER2....000003.........10:50:01.............Sta rted
    SERVER2....000003.........10:50:02.............Ack nowledged
    SERVER2....000003.........10:50:03.............Don e


    MACHINE...ALARMNUM..STARTED....ACKNOWLEDGED...DONE
    -----------...---------......---------.......-------------............------------
    SERVER1...000002.......09:15:05.....09:15:06...... .........09:15:07
    SERVER2...000003.......10:50:01.....10:50:02...... .........10:50:03

    I hope this makes it clearer.

    Ricky (again)
    Try this:

    select machine, alarmnum
    , max(decode(eventdetail, 'Started', detectiontime)) Started
    , max(decode(eventdetail, 'Acknowledged', detectiontime)) Acknowledged
    , max(decode(eventdetail, 'Done', detectiontime)) Done
    from table
    group by machine, alarmnum;

  4. #4
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    SELECT
    distinct A.MACHINE, A.ALARMNUM,
    (SELECT B.DETECTIONTIME FROM MY_TABLE B WHERE A.ALARMNUM=B.ALARMNUM AND b.EVENTDETAIL LIKE 'Started%') STARTED,
    (SELECT c.DETECTIONTIME FROM MY_TABLE c WHERE A.ALARMNUM=c.ALARMNUM AND c.EVENTDETAIL LIKE 'Acknowledged%') ACKNOWLEDGED,
    (SELECT d.DETECTIONTIME FROM MY_TABLE d WHERE A.ALARMNUM=d.ALARMNUM AND d.EVENTDETAIL like 'Done%') DONE
    FROM MY_TABLE A ;

    regards

  5. #5
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Oops With the solution

    select machine, alarmnum
    , max(decode(eventdetail, 'Started', detectiontime)) Started
    , max(decode(eventdetail, 'Acknowledged', detectiontime)) Acknowledged
    , max(decode(eventdetail, 'Done', detectiontime)) Done
    from table
    group by machine, alarmnum;

    I get
    ORA-00906: missing left parenthesis

    I don't understand why ???

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by pmscontact
    Oops With the solution

    select machine, alarmnum
    , max(decode(eventdetail, 'Started', detectiontime)) Started
    , max(decode(eventdetail, 'Acknowledged', detectiontime)) Acknowledged
    , max(decode(eventdetail, 'Done', detectiontime)) Done
    from table
    group by machine, alarmnum;

    I get
    ORA-00906: missing left parenthesis

    I don't understand why ???
    Because "table" is a reserved word. You need to replace "table" by the real table name!

  7. #7
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Of course
    it is ok thanks

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Query returns more than one row

    Hi
    I was reminded by this question.
    I had a feature in place, wherein there couldnt have been more than one child of the parent.
    During some meeting, this condition was relaxed, and it took me an hour to decipher what was causing my feature to break and where.
    I hope ur application wasnt as big as mine, coz making changes then it is a real pain.
    Thanx and Regards
    Aruneesh

  9. #9
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14
    Hi PMSCONTACT,
    Adding the DISTINCT still resulted in "ORA-01427: single-row subquery returns more than one row". I earlier opted for this syntax, but I don't understand why. It will hit me one day (as I keep banging my head on the wall).

    Hi TONY ANDREWS,
    Thanks for your suggestion. I eventually rewrote my query based on the DECODE function. Using the DECODE opened to me a different approach. I couldn't find the reason for the MAX, at first, then I guess this MAX function was used to settle the multiple ALARMNUM.... followed by the GROUP BY.... so cool.

    Hi Aruneesh,
    ... I am at a loss for words. :-)

    Have a good day to you all!
    Ricky

  10. #10
    Join Date
    Jul 2003
    Location
    Near Paris France
    Posts
    60
    Hi,

    "Distinct" is not to raise the error "ORA-01427" but just to get one result for each server and Alarnum.

    To raise the upon erropr, you must disctinct the subQueries by different letters
    SELECT B.DETECTIONTIME FROM MY_TABLE B...
    SELECT C.DETECTIONTIME FROM MY_TABLE C...
    SELECT D.DETECTIONTIME FROM MY_TABLE D...

    each subquery return one row, With always B on each query, I think that the optimizer "adds" the results and so returns more than one row.

    regards
    Philippe

  11. #11
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14
    Thanks Philippe! Because of your futher response, I did some more testing, and I was able to finally find the real problem.

    The subqueries DID indeed return more than one row (hence the ORA-01427). Because, per original database design, the combination of the ALARMNUM and EVENTDEATIL should be UNIQUE. As my environment was a DEVELOPMENT system, other users have re-inserted the same data hence, my search criteria ("WHERE A.ALARMNUM=B.ALARMNUM AND A.EVENTDETAIL LIKE...") results to multiple duplicate rows. (Yeah, yeah, I should have created a unique index in the first place).

    Hence, a "data" problem.

    'Learned a lot from this thread.

    Thanks again guys!

  12. #12
    Join Date
    Jun 2003
    Location
    HK
    Posts
    14
    Thanks Philippe! Becaus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •