Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ibib3 (IS/IT--Management) (OP)
10 Jun 09 10:16
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?

 
LKBrwnDBA (MIS)
10 Jun 09 10:28


You need to "OUTER JOIN" OP table to the MED table instead of the inline select.
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

Helpful Member!  SantaMufasa (TechnicalUser)
10 Jun 09 11:02
...Or, you can build a user-defined function (in PL/SQL) such as this:

CODE

create or replace function get_med (op_no_in number, role_in varchar2) return varchar2 is
    hold_meds varchar2(100);
begin
    for x in (select staff_id from med where opno = op_no_in and s_role = role_in) loop
        hold_meds := hold_meds||', '||x.staff_id;
    end loop;
    return ltrim(hold_meds,', ');
end;
/

Function created.
...then use the function in this adjustment to your original code:

CODE

col surgeons1 format a15
col surgeons2 format a15
SELECT
    OP.theatre AS theatrecode,
    OP.s_date AS opdate,
    OP.session_id AS sessionid,
    OP.opno,
    get_med (op.opno,'S1') Surgeons1,
    get_med (op.opno,'S2') Surgeons2
FROM OP
WHERE OP.consultant = 'LLOSN';

THEATRECOD OPDATE    SESSIONID        OPNO SURGEONS1       SURGEONS2
---------- --------- ---------- ---------- --------------- ---------------
SD01       18-NOV-04 SDU01           35073 FORJ            THOJ1
SJD01      23-NOV-04 BU002           35434 KIMM            CROB
SJD01      23-NOV-04 BU002           35452 KIMM
SJD01      24-NOV-04 BU003           35734 CROB, THOJ1     KIMM, FORJ
Let us know if you have questions.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

ibib3 (IS/IT--Management) (OP)
10 Jun 09 11:18
Thanks for replying LKBrwnDBA but doesn't that just give a row per surgeon with duplicate op details? I want to be able to have all the surgeons on a single row with the op data, using  one query if possible.

regards
ibib3 (IS/IT--Management) (OP)
10 Jun 09 11:20
Thanks SantaMufasa this looks to be just what I need.

regards
LKBrwnDBA (MIS)
10 Jun 09 11:48

Or, following Mufasa's lead, you could also do it without creating a function:

CODE

COL surgeon1 for a30 wra
COL surgeon2 for a30 wra
SELECT op.theatre AS theatrecode, op.s_date AS opdate,
       op.session_id AS sessionid, op.opno, m1.s1s AS surgeon1,
       m2.s2s AS surgeon2
  FROM op,
       (SELECT     opno,
                   SUBSTR (MAX (SYS_CONNECT_BY_PATH (staff_id, ',')), 2) s1s
              FROM (SELECT opno, staff_id,
                           ROW_NUMBER () OVER (PARTITION BY opno ORDER BY staff_id) rn
                      FROM med
                     WHERE s_role = 'S1')
        START WITH rn = 1
        CONNECT BY PRIOR rn = rn - 1 AND PRIOR opno = opno
          GROUP BY opno) m1,
       (SELECT     opno,
                   SUBSTR (MAX (SYS_CONNECT_BY_PATH (staff_id, ',')), 2) s2s
              FROM (SELECT opno, staff_id,
                           ROW_NUMBER () OVER (PARTITION BY opno ORDER BY staff_id) rn
                      FROM med
                     WHERE s_role = 'S2')
        START WITH rn = 1
        CONNECT BY PRIOR rn = rn - 1 AND PRIOR opno = opno
          GROUP BY opno) m2
 WHERE op.consultant = 'LLOSN' AND op.opno = m1.opno AND op.opno = m2.opno;
noevil
 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

LKBrwnDBA (MIS)
10 Jun 09 11:53

Ooops, you may need to "outer join" (+) the M2 query.
thumbsup2

 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

SantaMufasa (TechnicalUser)
10 Jun 09 11:55
LK,

I have not Oracle 8i upon which to run your code, but can Oracle Analytical Functions run on Oracle 8i?

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty."

LKBrwnDBA (MIS)
10 Jun 09 12:24


Ooops, don't think so...
Forgot it's 8i forum morning
.
 

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

LKBrwnDBA (MIS)
10 Jun 09 12:26

ibib3: GET RID OF 8i AS SOON AS POSSIBLE!
thumbsdown

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

ibib3 (IS/IT--Management) (OP)
10 Jun 09 15:32

Thanks for all of your help much appreciated.

8i does have some analytical functions, i've used lag before.  
LKBrwnDBA (MIS)
10 Jun 09 16:17

I haven't worked with 8i since 2004 and tend to forget it still exists!
.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close