Insert into / add

[mrossez]

Hi there! I am working on an Oracle 8 DB. We use a CRM Tool at our helpdesk and a special module which includes fields such as name, first name, address,...
The module can look up the DB for existing calls/requests.
My problem is: when a request is found in the DB, the new content is not added, but is replacing the former text.
Can you help? I am a novice in SQL. The following procedure has been generated by the module, which provides a GUI.
Thanks a lot in advance!
Matthieu
____________________
if (NB_AFFAIRES=1) then
update AFFAIRES set AFFAIRES.AF_REMARQUE=P_Nachricht,AFFAIRES.T9_CODE= P_Kontaktart,AFFAIRES.AF_INFO_COMP30=P_Vorgangsnum mer,AFFAIRES.T20_CODE=P_Medium_Antwort,AFFAIRES.AF _INFO_COMP25=P_VIN,AFFAIRES.AF_INFO_COMP11=P_EZD,A FFAIRES.AF_INFO_COMP6=P_Km_Stand,AFFAIRES.AF_INFO_ COMP16=P_NoVorgang,AFFAIRES.T7_CODE=65 where AF_CODE=V_AF_CODE;
else
select SEQ_AFFAIRES.nextval into V_AF_CODE from dual;
insert into AFFAIRES (AFFAIRES.AF_REMARQUE,AFFAIRES.T9_CODE,AFFAIRES.AF _INFO_COMP30,AFFAIRES.T20_CODE,AFFAIRES.AF_INFO_CO MP25,AFFAIRES.AF_INFO_COMP11,AFFAIRES.AF_INFO_COMP 6,AFFAIRES.AF_INFO_COMP16,AFFAIRES.T7_CODE,AFFAIRE S.AF_DATE_CREA,AFFAIRES.AF_DATE_CONT,AFFAIRES.REFE RENCE,AFFAIRES.AF_CODE) values (P_Nachricht,P_Kontaktart,P_Vorgangsnummer,P_Mediu m_Antwort,P_VIN,P_EZD,P_Km_Stand,P_NoVorgang,65,sy sdate,sysdate,V_REFERENCE,V_AF_CODE);
end if;

[skhanal]

I am not clear about the problem, do you want to insert a new record even if it is found in DB?

[mrossez]

Thanks for your answer.

I try to explain it better:

We have a webformular with fields like name, first name, aso...
The customer can inform us whether he is contacting us for a new problem or an existing problem. All existing problems have an ID-Number.

In case of a new call, all fields will be filled in in our DB.

In case of an existing call, only the new information /free text) given by the customer should be inserted.

At the moment, when a call number is found, the new information written by the customer is filled in the field "remarque". But the problem is: the whole field "remarque" is replaced. All information which were present before this field is updated are deleted.

I hope I made my case clearer. Sorry for my english!

Matthieu

[skhanal]

I am assuming that P_Nachricht is the new string. If you want to append the next string with the existing one you can use || operator.

update AFFAIRES set AFFAIRES.AF_REMARQUE=AF_REMARQUE || decode(P_Nachricht,null,'',P_Nachricht),
AFFAIRES.T9_CODE=P_Kontaktart,AFFAIRES.AF_INFO_COM P30=P_Vorgangsnummer,AFFAIRES.T20_CODE=P_Medium_An twort,AFFAIRES.AF_INFO_COMP25=P_VIN,AFFAIRES.AF_IN FO_COMP11=P_EZD,AFFAIRES.AF_INFO_COMP6=P_Km_Stand, AFFAIRES.AF_INFO_COMP16=P_NoVorgang,AFFAIRES.T7_CO DE=65 where AF_CODE=V_AF_CODE;

[mrossez]

Hi again,

I just read your reply, and just wanted to say thank you.

I am going to change the procedure right now.

Hope it'll work ! ;o)

Matth

[mrossez]

Hi again skhanal,

I wanted to thank you and inform you that the code is "valid". Everything works fine. Knowledge sharing is great! I have been reading SQL online curricula before finding your community and your forum, but I did not find such code. I assume that '' in P_Nachricht,null,'',P_Nachricht indicates a space between the former text and the latest content... I will try to write ' -- NEW MAIL -- ' and see what happens (I want to "highlight" the newest content). Or do you know how to add a blank line between the 2 contents?!...

Anyway, thanks again!

Matthieu

[skhanal]

You can concatenate chr(10) in between two values

oldvalue || chr(10) || newvalue

[mrossez]

Hallo Sir!

I had to make changes in our procedure: The field AFFAIRES.AF_REMARQUE could get a too small amount of characters.

First, I have been giving these values:

CREATE OR REPLACE PROCEDURE INSERT_CLIENTS2
(P_Name varchar2,P_Vorname varchar2,P_PLZ varchar2,P_Vorgangsnummer varchar2,P_Nachricht varchar2)
is
V_REFERENCE CLIENTS.REFERENCE%type;
V_AF_CODE AFFAIRES.AF_CODE%type;
V_HI_CODE HISTORIQUE.HI_CODE%type;
V_AG_CODE AGENDA.AG_CODE%type;
NB_AFFAIRES INTEGER;
NB_CLIENTS INTEGER;
NB_HISTORIQUE INTEGER;
NB_ITEM INTEGER;
Tp_val Date;
rem1 VARCHAR2(2000);
rem2 VARCHAR2(2000);
rem3 VARCHAR2(2000);
rem4 VARCHAR2(2000);
rem5 VARCHAR2(2000);

Begin
rem1 := SUBSTR(P_Nachricht,1,2000);
rem2 := SUBSTR(P_Nachricht,2001,2000);
rem3 := SUBSTR(P_Nachricht,4001,2000);
rem4 := SUBSTR(P_Nachricht,6001,2000);
rem5 := SUBSTR(P_Nachricht,8001,2000);
________________________

Second, I changed the command /Insert into AFFAIRES (AFFAIRES.AF_REMARQUE,AFFAIRES.T9_Code,...)/ in / (AFFAIRES.AF_REMARQUE,AFFAIRES.AF_REM_SUPP,AFFAIRE S.AF_REM_SUPP2,AFFAIRES.AF_REM_SUPP3,AFFAIRES.AF_R EM_SUPP4,AFFAIRES.T9_Code,...)

an replaced the value P_Nachricht by rem1,rem2,rem3,rem4,rem5

________________________

Then I changed the commands
/if (NB_AFFAIRES=1) then
update AFFAIRES set AFFAIRES.AF_REMARQUE=P_Nachricht,AFFAIRES.T9_CODE= P_Kontaktart,.../

in

/if (NB_AFFAIRES=1) then
update AFFAIRES set AFFAIRES.AF_REMARQUE=rem1,AFFAIRES.AF_REM_SUPP=rem 2,AFFAIRE.AF_REM_SUPP2=rem3,AFFAIRES.AF_REM_SUPP3= rem4,AFFAIRES.AF_RE_SUPP4=rem5,AFFAIRES.T9_CODE=P_ Kontaktart,...)/
______________________________

As such, I wrote the following command for the append function:

/if (NB_AFFAIRES=1) then
update AFFAIRES set AFFAIRES.AF_REMARQUE= rem1,AFFAIRES.AF_REM_SUPP=rem2,AFFAIRES.AF_REM_SUP P2=rem3,AFFAIRES.AF_REM_SUPP3=rem4,AFFAIRES.AF_REM _SUPP4=rem5 || decode(rem1,rem2,rem3,rem4,null,'',rem1,rem2,rem3, rem4),AFFAIRES.T9_CODE=P_Kontaktart,...)/

________________________

The program indicates the procedure is not valid.

Do you have a clue?

Thank you for the previous help, and many thanks if U have time to have a look at my new request...

Matth