Home » SQL & PL/SQL » SQL & PL/SQL » merge with concatenation update
merge with concatenation update [message #191694] Thu, 07 September 2006 15:39 Go to next message
basirana
Messages: 25
Registered: July 2006
Junior Member
below is the procedure used for merging the tables. if the row exist i want to perform concatenation update like

SET ET.CORP_CODE = ET.CORP_CODE||ES.CORP_CODE

but it is not performing the concatenation. please help to do concatenation update

CREATE OR REPLACE PROCEDURE TB_APPLICATION_REPORT
AS
BEGIN
MERGE INTO tb_application_demo_report ET
USING ( select APPLICATION_CODE , CORP_CODE from tb_application_demo) ES
ON (ET.APPLICATION_CODE = ES.APPLICATION_CODE)
WHEN MATCHED THEN
UPDATE
SET ET.CORP_CODE = ET.CORP_CODE||ES.CORP_CODE
WHEN NOT MATCHED THEN
INSERT
(ET.APPLICATION_CODE
,ET.CORP_CODE
)
VALUES
(ES.APPLICATION_CODE
,ES.CORP_CODE
);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
END;
Re: merge with concatenation update [message #191697 is a reply to message #191694] Thu, 07 September 2006 15:54 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
As already pointed out to you, you need to be doing a pivot, not a merge. Any why store these calculated values in another table? Just run a SQL query on the source table to pivot the data. Lose the redundant table and stored calculated data.
Re: merge with concatenation update [message #191866 is a reply to message #191694] Fri, 08 September 2006 07:43 Go to previous messageGo to next message
basirana
Messages: 25
Registered: July 2006
Junior Member
How to write pivot statement please help me for above example
Re: merge with concatenation update [message #191918 is a reply to message #191866] Fri, 08 September 2006 11:12 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Please search for 'pivot'. Many, many examples available.
Previous Topic: Raise Exception
Next Topic: FUNCTION not returns desired result - ?
Goto Forum:
  


Current Time: Fri Dec 09 13:42:02 CST 2016

Total time taken to generate the page: 0.10114 seconds