Sql Query...

[Former Member]

Hi ,

I have 2 database tables . one is vbep and the other one is zsodate. values will be changed in vbep . when we add a new records in vbep it will also adds the rows in zsodate. but our requirement is if one value is changed in vbep we have to updated the values in zsodate table by wrting the sql query by comparing the fields in vbeln and posnr .

give me update query how to find the exact row in zsodate and replace the value...

my code is this.

report z_test1 message-id xx .

tables : zsodate, vbap , vbep .

data : itab type standard table of zsodate with header line.

data : jtab type standard table of vbep with header line.

select-options : ordno for vbap-vbeln no intervals no-extension ,

itemno for vbap-posnr no intervals.

start-of-selection.

select * from zsodate into table itab where vbeln in ordno.

if sy-dbcnt = 0 .

message s999 with 'You Entered Wrong Order No ' .

exit.

endif.

select * from vbep into corresponding fields of table jtab

where vbeln in ordno.

loop at jtab.

write 😕 jtab-vbeln,

jtab-edatu,

jtab-posnr.

select * from zsodate into corresponding fields of zsodate

where vbeln = jtab-vbeln and posnr = jtab-posnr.

where vbeln = jtab-vbeln and posnr = jtab-posnr.

• loop at itab where vbeln = jtab-vbeln and posnr = jtab-posnr.

if sy-subrc = 0.

• write / 'vbep entries '.

*write :/4 jtab-etenr , 'vbep etenr',

• 15 jtab-edatu , 'vbep edatu'.

• uline.

*write:/2 zsodate-vbeln,

• 13 zsodate-posnr,

• 26 zsodate-etenr,

• 45 'has been updated'.

move jtab-etenr to zsodate-etenr.

write jtab-edatu to zsodate-origdate mm/dd/yyyy.

zsodate-mandt = sy-mandt.

zsodate-uname = sy-uname.

modify zsodate.

write:/2 zsodate-vbeln,

13 zsodate-posnr,

26 zsodate-etenr,

45 'has been updated.'.

endif.

endselect.

[Former Member]

Hi,

You can do the same in below mentioned manner.

There are two ways where you have to update your ZSODATA Table.

Once when there is data in VBEP and you dont have the record in the ZSODATA.

For which Your steps should be :

Step - 1.

**********

Select Data from the VBEP table in an internal table i.e which satisfies your selection criteria of Order No.

Step 2. A

*********

Select Data from the ZSODATA table in an internal table with the matching selection criteria used for the above query.If no record found INSERT the record in ZSODATA.

Step 2. B

***********

Select Data from the ZSODATA table in an internal table with the matching selection criteria used for the above query.If record found extend your search criteria for more fields and based on the record found or not found update those fields in ZSODATA table.

Thanks,

Mayank

[Former Member]

*define a one more work area same as of zsodate say wa.<br>

*your processing in loop will be as follows:-<br>

loop at jtab. <br>

*<b>No need to put the select query inside thr loop</b><br>

_________________Not Required________________<br>

select * from zsodate into corresponding fields of zsodate <br>

where vbeln = jtab-vbeln and posnr = jtab-posnr. <br>

where vbeln = jtab-vbeln and posnr = jtab-posnr. <br>

________________End of not required______________<br>

<b>* use read statement, it will improve performanace<br>

read table itab with key ordno = jtab-ordno.<br>

if sy-subrc eq 0.<br>

move corresponding jtab to wa.<br>

wa-mandt = sy-mandt. <br>

wa-uname = sy-uname. <br>

modify zsodate from wa.. <br>

endloop.<br>