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;
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!
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?!...
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...