topic Re: MODIFY AND UPDATE STATEMENTS in Application Development and Automation Discussions

MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:01:40 GMT

HELLO EXPERTS

I have defined a custom table with some fields , first few fields are getting updated from the standard tables and the remainaing fields from the application server file , i actually used the modify and update statements resp ,

its not working properly in the sense that everytime i run the program , only this data is seen the previous data is lost , the file in the production server will be updated daily and even the data in the standard tables may change .

I actually want to change the code so as to give a single modify statement with the internal table containing all fields instead of modifying the table two times .

here I am posting my code.......

REPORT ZSDSOX025_UPLOAD MESSAGE-ID /interfaces.

PARAMETERS: filenm1 type rlgrap-filename default

'/interfaces/sox025.txt'.

----

DATA TYPES DECLARATION

----

TYPES : BEGIN OF ttab_split,

rfcno(8) TYPE c,

phase(10) TYPE c,

transno(12) TYPE c,

transdes(30) TYPE c,

start(25) TYPE c,

ownerid(8) TYPE c,

name(17) TYPE c,

changelevel(2) TYPE c,

urgency(9) TYPE c,

changedept(16) TYPE c,

impgroup(15) TYPE c,

application(15) TYPE c,

date(12) TYPE c,

time(15) TYPE c,

END OF ttab_split.

----

INTERNAL TABLES DECLARATION

----

ITAB is like table ZSOX025. ITAB1 has elements similar to those in
E070 and E07T. itab2 is like zsox025. First join E070, E07T & E07C

*and put data in ITAB1. Move data from ITAB1 to ITAB to itab2. If

*ITAB[]is not initial update ZSOX025

DATA : BEGIN OF ITAB OCCURS 0,

TRNUMBER LIKE ZSOX025-TRNUMBER,

TRDESC LIKE ZSOX025-TRdesc,

TRDATE LIKE ZSOX025-TRDATE,

TRTIME LIKE ZSOX025-TRTIME,

TROWNER LIKE ZSOX025-TROWNER,

TARSYSTEM LIKE E070-TARSYSTEM,

END OF ITAB.

DATA : itab2 TYPE STANDARD TABLE OF ZSOX025 WITH HEADER LINE.

DATA : itab_appln TYPE STANDARD TABLE OF ZSOX025 WITH HEADER LINE.

DATA : itab_success TYPE STANDARD TABLE OF ZSOX025 WITH HEADER LINE.

DATA : itab_mismatch TYPE STANDARD TABLE OF ZSOX025 WITH HEADER LINE.

DATA : BEGIN OF ITAB1 OCCURS 0,

TRKORR like E070-TRKORR,

AS4TEXT like E07T-AS4TEXT,

AS4DATE like E070-AS4DATE,

AS4TIME like E070-AS4TIME,

AS4USER like E070-AS4USER,

TARSYSTEM like E070-TARSYSTEM.

DATA : END OF ITAB1.

DATA : BEGIN OF itab_raw OCCURS 0,

rec(1000) type c.

DATA : END OF itab_raw.

DATA : itab_split TYPE TABLE OF ttab_split WITH HEADER LINE.

----

VARIABLES DECLARATION

----

DATA : filenm TYPE rlgrap-filename VALUE '/interfaces/sox025.txt'.

----

START-OF-SELECTION

----

START-OF-SELECTION.

PERFORM START-OF-SEL.

PERFORM UPLOAD_DATA.

PERFORM CONVERSION.

&----

*& FORM START-OF-SEL

&----

FORM START-OF-SEL.

select transports from e070 & description from E07t & tarclient from
E070c using join. Update in ZSOX025.

SELECT e070_{trkorr e070}as4user e070_{as4date e070}as4time e070~tarsystem

e07t~as4text INTO CORRESPONDING FIELDS OF TABLE ITAB1

FROM ( e070 LEFT JOIN e070c ON e070_{trkorr = e070c}trkorr )

LEFT JOIN e07t ON e070_{trkorr = e07t}trkorr.

LOOP AT ITAB1.

ITAB-TRNUMBER = ITAB1-TRKORR.

ITAB-TRDESC = ITAB1-AS4TEXT.

ITAB-TRDATE = ITAB1-AS4DATE.

ITAB-TRTIME = ITAB1-AS4TIME.

ITAB-TROWNER = ITAB1-AS4USER.

ITAB-TARSYSTEM = ITAB1-TARSYSTEM.

append itab.

ENDLOOP.

LOOP AT ITAB.

ITAB2-TRNUMBER = ITAB-TRNUMBER.

ITAB2-TRDESC = ITAB-TRDESC.

ITAB2-TRDATE = ITAB-TRDATE.

ITAB2-TRTIME = ITAB-TRTIME.

ITAB2-TROWNER = ITAB-TROWNER.

append itab2.

ENDLOOP.

MODIFY ZSOX025 FROM TABLE ITAB2.

ENDFORM. " START-OF-SEL

*----

FORM UPLOAD_DATA

*----

FORM UPLOAD_DATA.

upload data from the SS file in the application server

filenm = filenm1.

OPEN DATASET filenm FOR INPUT IN TEXT MODE.

IF sy-subrc <> 0.

Message e006 with 'file could not be opened'.

ELSE.

DO.

READ DATASET filenm INTO itab_raw.

IF sy-subrc = 0.

APPEND itab_raw.

CLEAR itab_raw.

ELSE.

EXIT.

ENDIF.

ENDDO.

ENDIF.

CLOSE DATASET filenm.

ENDFORM.

*----

Form conversion

*----

FORM conversion.

DATA : idate type sydatum.

DATA : itime type syuzeit.

DATA : date(10),time(8),ses(2).

LOOP AT itab_raw.

CLEAR itab_split.

SPLIT itab_raw at '^' into

itab_split-rfcno

itab_split-phase

itab_split-transno

itab_split-transdes

itab_split-start

itab_split-ownerid

itab_split-name

itab_split-changelevel

itab_split-urgency

itab_split-changedept

itab_split-impgroup

itab_split-application.

append itab_split.

ENDLOOP.

LOOP AT itab_split.

SPLIT itab_split-start AT SPACE INTO date time ses.

CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'

EXPORTING

date_external = date

IMPORTING

date_internal = idate

EXCEPTIONS

date_external_is_invalid = 1

OTHERS = 2.

IF sy-subrc <> 0.

MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

ENDIF.

CALL FUNCTION 'CONVERT_TIME_INPUT'

EXPORTING

INPUT = time

PLAUSIBILITY_CHECK = 'X'

IMPORTING

OUTPUT = itime

EXCEPTIONS
PLAUSIBILITY_CHECK_FAILED = 1
WRONG_FORMAT_IN_INPUT = 2
OTHERS = 3

IF SY-SUBRC <> 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

itab_split-date = idate.

itab_split-time = itime.

MODIFY itab_split.

ENDLOOP.

IF NOT ITAB_split[] IS INITIAL.

LOOP AT itab_split.

UPDATE zsox025 SET rfcno = itab_split-rfcno

phase = itab_split-phase

transno = itab_split-transno

transdesc = itab_split-transdes

trandate = itab_split-date

trantime = itab_split-time

tranowner = itab_split-ownerid

tranname = itab_split-name

changelevel = itab_split-changelevel

urgency = itab_split-urgency

changedept = itab_split-changedept

impgroup = itab_split-impgroup

application = itab_split-application

WHERE trnumber = itab_split-transno.

ENDLOOP.

ENDIF.

ENDFORM.

Please can anyone help me in this issue.....

SRI

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:17:05 GMT

Try using CLIENT SPECIFIED forUPDATE AND MODIFY Statements:

For e.g.:

UPDATE SFLIGHT CLIENT SPECIFIED

SET SEATSOCC = SEATSOCC + 3

WHERE MANDT = '002' AND

WHERE CARRID = 'LH' AND

CONNID = '0400' AND

FLDATE = '19950228'.

Thanks,

Santosh

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:20:59 GMT

The table is getting updated now but if i run the program again with the different data , the previous data is lost.

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:26:36 GMT

Hai after Update statement

use Commit work statement.

then the update process will complete.

Regards

Sreeni

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:27:15 GMT

Hi,

I didnt actually understand what your problem was but I could say if you dont want two statements, just upload the data from the App Server first and then collect all the corresponding records from the two tables ITAB2 and ITAB_SPLIT and write a single correct 'Modify' statment.

This will be good even from a programming prospective as you hit the DB only once.

Hope this helps.

Ketan

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:29:25 GMT

hi,

use INSERT instead of UPDATE...

update overwirte on the previous records....

insert append to the previous records...

Regards

CNU

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:37:38 GMT

ketan,

i want to try in the way u said but i donno how to write the code exactly for that. This is an emergency request so can you please help me in writing that

Thanks

Sri

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:39:18 GMT

Please check If your database table contains a line with the same primary key as the line to be inserted, if yes, MODIFY works like UPDATE, that is the line is changed.

For more info, refer this link:

http://unison.wcomnet.com:8082/saphelp/helpdata/EN/fc/eb3b64358411d1829f0000e829fbfe/frameset.htm

Thanks,

Santosh

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 19:51:00 GMT

See as some of our peers said, you have to first see what is your business requirment. If you want that every time a changed entry should replace the existing entry, then you need to write a MODIFY.

If you want that a totally new and importantly a 'UNIQUE' entry should be added to the existing records, you have to use INSERT statment.

Now the logic for the single MODIFY could be .....

First Loop at the itab_split after you have done all the data formatting and then check the 'Key' fields of the table are simmilar to the ITAB2. I think in your case it is the TRNUMBER field.

If thats correct, then create a new table or add to the fields to the exiting ITAB2 , the entries in the ITAB_SPLIT but for the same Key field entries.

Finally when you have to comprehensive table, ITAB2 ( The final table which contains details from both the tables) , then MODIFY ZSOX025 table as

MODIFY ZSOX025 FROM TABLE ITAB2.

Remember first decide if you want to INSERT or MODIFY and then follow the same logic.

Hope this help.

Ketan

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 20:17:47 GMT

ketan,

Its like i need one modify statement at the end with itab2 containing fields from both sides just like you have said. Say itab2 is containing some records with data from first few fields(from standard table) ... itab_split is containing data from the remaining fields .. how to add the entries of itab_split to itab2 for the same key fields entries .. i donno how to give that loop exactly .. how do i modify the below pasted code

IF NOT ITAB_split[] IS INITIAL.

LOOP AT itab_split.

update zsox025 SET rfcno = itab_split-rfcno

phase = itab_split-phase

transno = itab_split-transno

transdesc = itab_split-transdes

trandate = itab_split-date

trantime = itab_split-time

tranowner = itab_split-ownerid

tranname = itab_split-name

changelevel = itab_split-changelevel

urgency = itab_split-urgency

changedept = itab_split-changedept

impgroup = itab_split-impgroup

application = itab_split-application

WHERE trnumber = itab_split-transno.

COMMIT WORK.

ENDLOOP.

ENDIF.

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 20:30:19 GMT

Create a table say ITAB_FINAL which contains both the table fields.

Then do something like this...

LOOP AT ITAB_SPLIT .

READ TABLE ITAB2 with key TRNUMBER = ITAB_SPLIT-rfcno. ( If the rfcno and the TRNUMBER are haveing same values, add any other fields also which match )

IF sy-subrc = 0.

MOVE all the entries from the ITAB2 table which is read in the work area to the ITAB_FIANL table. Similarly, MOVE all the entries from ITAB_SPLIT to the ITAB_FIANL table.

APPEND the ITAB_FIANL table.

ENDIF.

ENDLOOP.

Then use the ITABFIANL to modify the Z table.

Thanks,

Ketan.

Pls award points is useful........

Re: MODIFY AND UPDATE STATEMENTS

Former Member — Fri, 19 Jan 2007 21:04:20 GMT

i changed the loop to something like this but its still giving me a run time error

IF NOT ITAB_split[] IS INITIAL.

LOOP AT itab_split.

read table itab2 with key trnumber = itab_split-transno.

if sy-subrc = 0.

itab2-rfcno = itab_split-rfcno.

itab2-phase = itab_split-phase.

itab2-transno = itab_split-transno.

itab2-transdesc = itab_split-transdes.

itab2-trandate = itab_split-date.

itab2-trantime = itab_split-time.

itab2-tranowner = itab_split-ownerid.

itab2-tranname = itab_split-name.

itab2-changelevel = itab_split-changelevel.

itab2-urgency = itab_split-urgency.

itab2-changedept = itab_split-changedept.

itab2-impgroup = itab_split-impgroup.

itab2-application = itab_split-application.

modify itab2.

endif.

ENDLOOP.

modify zsox025 from table itab2.

commit work.

ENDIF.