Home » SQL & PL/SQL » SQL & PL/SQL » errors in procedure
errors in procedure [message #267410] Thu, 13 September 2007 04:08 Go to next message
sankara.gattu
Messages: 10
Registered: August 2007
Location: bangalore
Junior Member

Hi Team,

Here i am sending procedure for updating the data.

Its not updating select statement returns multiple rows.for this case normally used cursor but still am facing .

Is there any method other than cursors. send me one example.

kindly do the needful.

Many thanks in advance......

Thanks,
Sankara

[mod-edit] illiterate IM speak words removed.

[Updated on: Thu, 13 September 2007 12:27] by Moderator

Report message to a moderator

Re: errors in procedure [message #267413 is a reply to message #267410] Thu, 13 September 2007 04:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I can't download the attachemnt.

Just post and format it in the message.
Re: errors in procedure [message #267414 is a reply to message #267413] Thu, 13 September 2007 04:17 Go to previous messageGo to next message
sankara.gattu
Messages: 10
Registered: August 2007
Location: bangalore
Junior Member

CREATE OR REPLACE PROCEDURE SSEUPDATESTATUS
IS

BEGIN
UPDATE TXN_SERVICE_ORDER SET STATUS_ID=
(SELECT B.STATUS_ID --,B.STATUS,A.BEP_STATUS,A.BES_STATUS
FROM TXN_STATUS B,PSG_STATUS_MAPPING A
WHERE A.BEP_STATUS=B.STATUS
GROUP BY B.STATUS_ID)
WHERE BEP_SERVICEORDER_ID IN (SELECT BEP_SERVICEORDER_ID
FROM SSE_UPDATE_STATUS);

COMMIT;

UPDATE TXN_SERVICE SET SERVICE_STATUS =
(SELECT STATUS FROM
(SELECT A.SERVICE_ID,B.STATUS_ID,D.STATUS
FROM TXN_SERVICE A,TXN_SERVICE_ORDER B,TXN_SERVICE_ORDER C,TXN_STATUS D
WHERE A.SERVICE_ID = B.SERVICE_ID
AND B.SERVICE_ORDER_ID = C.SERVICE_ORDER_ID
AND C.STATUS_ID = D.STATUS_ID
GROUP BY A.SERVICE_ID,B.STATUS_ID,D.STATUS) TMP
WHERE TMP.SERVICE_ID = TXN_SERVICE.SERVICE_ID)
WHERE SERVICE_ID IN (SELECT AB.SERVICE_ID
FROM TXN_SERVICE_ORDER AB,SSE_UPDATE_STATUS SS
WHERE AB.BEP_SERVICEORDER_ID = SS.BEP_SERVICEORDER_ID);

COMMIT;

END;
Re: errors in procedure [message #267417 is a reply to message #267414] Thu, 13 September 2007 04:24 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Its not updating bcz select statement returns multiple rows
Join the table (in the update) with the table in the select clause, so that it will get only one row.

Please format your posts.

By
Vamsi
Previous Topic: cursor query
Next Topic: Reference to uninitialized collection
Goto Forum:
  


Current Time: Wed Dec 07 04:40:56 CST 2016

Total time taken to generate the page: 0.11952 seconds