adding field values from internal table

[Former Member]

Hello all,

I need to add values from a field (CUST_GRP1) of an internal table (TB_CUST_COPA) into a "real" table (/BI0/PCUST_SALES). The CUST_GRP1 should be added in /BI0/PCUST_SALES when there's a match on CUST_SALES, DISTR_CHAN and DIVISION between the two tables.

How should the code look? What's wrong in the code below?

-

---

LOOP AT TB_CUST_COPA.

SELECT CUST_GRP1 FROM /BI0/PCUST_SALES UP TO 1 ROWS

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

/BI0/PCUST_SALES-CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

UPDATE /BI0/PCUST_SALES.

COMMIT WORK.

ENDLOOP.

-

---

Best regards,

Fredrik

[dani_mn]

Hi try this.

LOOP AT TB_CUST_COPA.

SELECT * FROM /BI0/PCUST_SALES UP TO 1 ROWS

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

IF sy-subrc = 0.

/BI0/PCUST_SALES-CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

UPDATE /BI0/PCUST_SALES.

COMMIT WORK.

ENDIF.

ENDLOOP.

Regards,

Wasim Ahmed

-

---

[Former Member]

Hello,

Thanks for all your code-examples!

I followed the code from Wasim, but I get a short dump directly after the COMMIT WORK. Why is this and how can I solve it?

I only added ENDSELECT. in the code from Wasim... Was this ok?

-

---

LOOP AT TB_CUST_COPA.

SELECT * FROM /BI0/PCUST_SALES UP TO 1 ROWS

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

IF sy-subrc = 0.

/BI0/PCUST_SALES-CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

UPDATE /BI0/PCUST_SALES.

COMMIT WORK.

ENDIF.

ENDSELECT.

ENDLOOP.

-

---

Best regards,

Fredrik

[Former Member]

HI Fredrik ,

why to fetch the database 2 times for 1 particular task ? as UPDATE itself will take care of updating the values of a particular fields in the database table based on the condition given in WHERE clause.

the current logic may some times takes more processing time, if the no of records is more, as it should do 2 times database hits (1 for fetching & 1 for updating)

regards

srikanth

Message was edited by: Srikanth Kidambi

[Former Member]

You can use as

Loop at...
SELECT FOR UPDATE...
endloop.

No Commit while using

SELECT FOR UPDATE

or you can use

Loop at...
UPDATE tablename ......where..
COMMIT WORK 

endloop.

Cheers,

Thomas.

[Former Member]

hi

use MODIFY instead of update

MODIFY dbtab from itab index sy-tabix.

[Former Member]

Do as follows:

data: begin of it_cust_sales occurs 0.

include structure /BI0/PCUST_SALES.

data: end of it_cust_sales.

if not TB_CUST_COPA[] is initial.

select * from /BI0/PCUST_SALES into table it_cust_sales

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

endif.

LOOP AT TB_CUST_COPA.

read table it_cust_sales with key CUST_SALES = TB_CUST_COPA-CUST_SALES

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN DIVISION = TB_CUST_COPA-DIVISION

OBJVERS = 'A'.

if sy-subrc = 0.

/BI0/PCUST_SALES-CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

UPDATE /BI0/PCUST_SALES.

COMMIT WORK.

endif.

ENDLOOP.

Regards,

Ravi

[Former Member]

i think you no need to select anything from the database before updating. if you pass all the 3 fields in the where condition of UPDATE, it will take care of everything.

UPDATE <target> SET <set1> <set 2> ... [WHERE <cond>].

LOOP AT TB_CUST_COPA.

UPDATE /BI0/PCUST_SALES

SET CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

ENDLOOP.

This logic will do that.

regards

srikanth

[Former Member]

Hello,

I chose to use the code from Srikanth. This worked fine and updated the target table. Should the "update" be changed into "modify"? Why?

-

---

LOOP AT TB_CUST_COPA.

UPDATE /BI0/PCUST_SALES

SET CUST_GRP1 = TB_CUST_COPA-CUST_GRP1.

WHERE CUST_SALES = TB_CUST_COPA-CUST_SALES AND

DISTR_CHAN = TB_CUST_COPA-DISTR_CHAN AND

DIVISION = TB_CUST_COPA-DIVISION AND

OBJVERS = 'A'.

ENDLOOP.

-

---

Best regards,

Fredrik

[Former Member]

HI,

we should NOT use MODIFY in your case, as MODIFY will be used for Inserting/changing records.

if the record does not exists, it will insert the record.

if the record exists, it will change the values.

but UPDATE will do only works if the matching record exists in the database, it wont create at all.

here is the description of both(copied from sap library)

Changing Lines

The Open SQL statement for changing data in a database table is:

UPDATE <target> <lines>.

It allows you to change one or more lines in the database table <target>. You can only change lines in an ABAP Dictionary view if it only contains fields from one table, and its maintenance status is defined as Read and change. You may specify the database table <target> either statically or dynamically.

Changing Lines Column by Column

To change certain columns in the database table, use the following:

UPDATE <target> SET <set1> <set 2> ... [WHERE <cond>].

The WHERE clause determines the lines that are changed. If you do not specify a WHERE clause, all lines are changed. The expressions <set i > are three different SET statements that determine the columns to be changed, and how they are to be changed:

Inserting or Changing Lines

To insert lines into a database table regardless of whether there is already a line in the table with the same primary key, use the following:

MODIFY <target> <lines>.

If the database table contains no line with the same primary key as the line to be inserted, MODIFY works like INSERT, that is, the line is added.

If the database already contains a line with the same primary key as the line to be inserted, MODIFY works like UPDATE, that is, the line is changed.

regards

srikanth

Message was edited by: Srikanth Kidambi

[Former Member]

Thank you for the information and support!

Best regards,

Fredrik