Hi
can any one help me please,
I have two tables, OP that holds surgical operation details and MED that holds the medical staff associated with an operation.
The MED.s_role indicates the role of the medical staff eg. S1 - Principle Surgeon and S2-Assistant Surgeon.
When I first ran the query below it worked for one consultant but when I tried a second consultant -'LLOSN' it is bringing back the error message ORA-01427: single-row subquery returns more than one row.
After checking I found out that it is possible to have more than one surgeon sharing a particular role hence the error message.
Query:
SELECT
OP.theatre AS theatrecode,
OP.s_date AS opdate,
OP.session_id AS sessionid,
OP.opno,
(select MED.staff_id
from MED
where OP.opno = MED.opno and MED.s_role = 'S1') Surgeon1,
(select MED.staff_id
from MED
where OP.opno = MED.opno and MED.s_role = 'S2') surgeon2
FROM
OP
WHERE
OP.consultant = 'LLOSN'
Result:
THEAT OPDATE SESSI OPNO SURGEON1 SURGEON2
----- --------- ----- ---------- --------- ---------
SD01 18-NOV-04 SDU01 35073 FORJ THOJ1
SJD01 23-NOV-04 BU002 35434 KIMM CROB
SJD01 23-NOV-04 BU002 35452 KIMM
SJD02 23-NOV-04 BU004 35475 CROB LLOSN
SJG03 23-NOV-04 GGU02 35538 LLOSN
SJG03 23-NOV-04 GGU02 35541 LLOSN
SJG03 23-NOV-04 GGU02 35546 THOJ1
SJG03 23-NOV-04 GGU02 35548 LLOSN
SJG03 23-NOV-04 GGU02 35552 LLOSN
SJD01 24-NOV-04 BU003 35730 LLOSN CROB
SJD01 24-NOV-04 BU003 35733 LLOSN
ERROR:
ORA-01427: single-row subquery returns more than one row
Is there a way of showing both surgeons who are sharing the role and stop the error message from occuring?
can any one help me please,
I have two tables, OP that holds surgical operation details and MED that holds the medical staff associated with an operation.
The MED.s_role indicates the role of the medical staff eg. S1 - Principle Surgeon and S2-Assistant Surgeon.
When I first ran the query below it worked for one consultant but when I tried a second consultant -'LLOSN' it is bringing back the error message ORA-01427: single-row subquery returns more than one row.
After checking I found out that it is possible to have more than one surgeon sharing a particular role hence the error message.
Query:
SELECT
OP.theatre AS theatrecode,
OP.s_date AS opdate,
OP.session_id AS sessionid,
OP.opno,
(select MED.staff_id
from MED
where OP.opno = MED.opno and MED.s_role = 'S1') Surgeon1,
(select MED.staff_id
from MED
where OP.opno = MED.opno and MED.s_role = 'S2') surgeon2
FROM
OP
WHERE
OP.consultant = 'LLOSN'
Result:
THEAT OPDATE SESSI OPNO SURGEON1 SURGEON2
----- --------- ----- ---------- --------- ---------
SD01 18-NOV-04 SDU01 35073 FORJ THOJ1
SJD01 23-NOV-04 BU002 35434 KIMM CROB
SJD01 23-NOV-04 BU002 35452 KIMM
SJD02 23-NOV-04 BU004 35475 CROB LLOSN
SJG03 23-NOV-04 GGU02 35538 LLOSN
SJG03 23-NOV-04 GGU02 35541 LLOSN
SJG03 23-NOV-04 GGU02 35546 THOJ1
SJG03 23-NOV-04 GGU02 35548 LLOSN
SJG03 23-NOV-04 GGU02 35552 LLOSN
SJD01 24-NOV-04 BU003 35730 LLOSN CROB
SJD01 24-NOV-04 BU003 35733 LLOSN
ERROR:
ORA-01427: single-row subquery returns more than one row
Is there a way of showing both surgeons who are sharing the role and stop the error message from occuring?