topic Update table after join in Application Development and Automation Discussions

Update table after join

Former Member — Thu, 26 Feb 2009 09:23:09 GMT

Hello Dear Experts

I have made the program with simple join:

REPORT XXX.

Tables: AAA, BBB, CCC, DDD.

select single

b~field2

c~field3

a~field1

into (DDD-field4, DDD-field5, DDD-field6)

from AAA AS a

INNER JOIN BBB AS b ON a_{field1 = b}field2

AND a_{fieldx = b}fieldx

INNER JOIN CCC AS c ON a_{field1 = c}field1

AND a_{fieldx = c}fieldx

WHERE a~fieldx = 'A'.

update CCC from CCC.

commit work.

The question is, how can I update the table CCC after the join is made?

Regards

Robert

Re: Update table after join

Former Member — Thu, 26 Feb 2009 09:26:19 GMT

Hi,

you try to update CCC with CCC. How should this change somethink??

You need to update CCC from an other workarea.

Regards

Nicole

Re: Update table after join

Former Member — Thu, 26 Feb 2009 09:27:47 GMT

Create a Maintenance View in SE11 using the condition specified in the select query.

Use that View to update.

Re: Update table after join

Former Member — Thu, 26 Feb 2009 09:29:50 GMT

Hi Robert,

Use it like this.

Update ccc from table CCC.

Much Regards,

Amuktha.

Re: Update table after join

Former Member — Thu, 26 Feb 2009 09:57:45 GMT

Sorry, It was small mistake in the explanation : should be update DDD from ddd....

Regards

Robert

Re: Update table after join

Former Member — Thu, 26 Feb 2009 11:03:42 GMT

Re: Update table after join

naveen_inuganti2 — Thu, 26 Feb 2009 11:08:37 GMT

Better way is,

Create the work area to save three variables of DDD and then Update the DDD table.

__Naveen Inuganti.

Re: Update table after join

Former Member — Thu, 26 Feb 2009 11:21:41 GMT

Tables: AAA, BBB, CCC, DDD.

data: idata TYPE STANDARD TABLE OF ddd

data: wa_data TYPE ddd.

select

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field1 = b}field2

AND a_{fieldx = b}fieldx

INNER JOIN CCC AS c ON a_{field1 = c}field1

AND a_{fieldx = c}fieldx

WHERE a~fieldx = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update ddd from wa_data.

endloop.

Edited by: Nicole Lorenz on Feb 26, 2009 6:22 AM

Edited by: Nicole Lorenz on Feb 26, 2009 6:23 AM

Re: Update table after join

former_member222860 — Thu, 26 Feb 2009 11:28:03 GMT

Hi,

Pl.check, if it works:

Tables: AAA, BBB, CCC, DDD.

select single
b~field2
c~field3
a~field1
into (DDD-field4, DDD-field5, DDD-field6)
from AAA AS a
INNER JOIN BBB AS b ON a~field1 = b~field2
AND a~fieldx = b~fieldx
INNER JOIN CCC AS c ON a~field1 = c~field1
AND a~fieldx = c~fieldx
WHERE a~fieldx = 'A'.

UPDATE DDD SET: field4 = ddd-field4,
                field5 = ddd-field5,
                field6 = ddd-field6.

thanks\

Mahesh

Re: Update table after join

Former Member — Thu, 26 Feb 2009 13:37:58 GMT

I've created the select:

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data TYPE DDD.

select

b~field1

c~field2

a~field3

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

I've got the error: you cannot use an internal table as a work area. What is the reason for this problem?

Robert

Re: Update table after join

Former Member — Thu, 26 Feb 2009 15:15:12 GMT

Data: wa_data TYPE DDD.

is the mistake.

It should be:

wa_data like line of idata.

Regards,

Bobi

Re: Update table after join

Former Member — Thu, 26 Feb 2009 16:55:02 GMT

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data like line of idata.

select

b~field1

c~field2

a~field3

into idata

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

The same error: You cannot use an internal table as work area.

Robert

Re: Update table after join

Former Member — Thu, 26 Feb 2009 17:17:47 GMT

Please use code tags. Try this:

SELECT b~field1 c~field2 a~field3
  INTO wa_data                          <====
  FROM aaa AS a
    INNER JOIN bbb AS b ON a~field4 = b~field1
                       AND a~field3 = b~field5
    INNER JOIN ccc AS c ON a~field6 = c~field2
                       AND a~field3 = c~field7
  WHERE a~field3 = 'A'.
  COLLECT idata.
ENDSELECT.

Rob

Re: Update table after join

Former Member — Thu, 26 Feb 2009 18:05:58 GMT

Hi,

use the statements below to compare the mistake you have done in coding

Tables: BBB, CCC, AAA, DDD.
Data: idata TYPE STANDARD TABLE OF DDD.
Data: wa_data like line of idata.

select
b~field1
c~field2
a~field3
"into idata                           idata is declared as internal table of DDD without header line. so this 
" created a problem because you have not entered into table addition. Instead of this statement use the 
" statement given below
into wa_data
from AAA AS a
INNER JOIN BBB AS b ON a~field4 = b~field1
AND a~field3 = b~field5
INNER JOIN CCC AS c ON a~field6 = c~field2
AND a~field3 = c~field7
WHERE a~field3 = 'A'.
collect wa_data to idata.                       " also change this statement from collect idata to the given 
" statement
endselect.

loop at idata into wa_data.
update DDD from wa_data.
endloop.

Regards,

Siddarth

Re: Update table after join

Former Member — Fri, 27 Feb 2009 08:43:12 GMT

Thank You Siddarth

I have modified the select:

Tables: BBB, CCC, AAA, DDD.

Data: idata TYPE STANDARD TABLE OF DDD.

Data: wa_data like line of idata.

select

b~field1

c~field2

a~field3

into wa_data

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

collect wa_data into idata.

endselect.

loop at idata into wa_data.

update DDD from wa_data.

endloop.

I don't have any syntax error but update doesn't work. it works for this select (without using int tables and wa):

Tables: BBB, CCC, AAA, DDD.

select

b~field1

c~field2

a~field3

into (DDD-field, DDD-field, DDD-field)

from AAA AS a

INNER JOIN BBB AS b ON a_{field4 = b}field1

AND a_{field3 = b}field5

INNER JOIN CCC AS c ON a_{field6 = c}field2

AND a_{field3 = c}field7

WHERE a~field3 = 'A'.

update DDD.

endselect.

Regards

Robert

Re: Update table after join

Former Member — Fri, 27 Feb 2009 08:58:44 GMT

Hi Robert,

firstly please check the key fields of table DDD and also check if wa is changing the value of the key fields in this case update will not work,

if still you didn't understand pls. paste the key fields of DDD table and one example value of the table

and also the fields and value of wa.

Regards,

Siddarth

Re: Update table after join

Former Member — Fri, 27 Feb 2009 09:04:19 GMT

Thank You Siddarth

The both selects work now.

Regards

Robert