topic Re: adding field values from internal table in Application Development and Automation Discussions

adding field values from internal table

Former Member — Wed, 07 Jun 2006 11:16:33 GMT

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

Re: adding field values from internal table

dani_mn — Wed, 07 Jun 2006 11:24:36 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 11:25:54 GMT

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.

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 11:26:24 GMT

use MODIFY instead of update

MODIFY dbtab from itab index sy-tabix.

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 11:27:01 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 11:29:51 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 12:09:39 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 12:15:13 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 12:23:33 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 12:29:37 GMT

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

Re: adding field values from internal table

Former Member — Wed, 07 Jun 2006 12:37:03 GMT

Thank you for the information and support!

Best regards,

Fredrik