topic Re: how to write select statement between two tables in Application Development and Automation Discussions

how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:50:12 GMT

hi,

i need to do comparision between two table for each records.ex:

table1:

regid regno ind

1 1001

1 1002

1 1003

1 1004

and table2:

regid regno

1 1002

1 1005

i need to select first row from table and loop for values in second table, if the values found first record , the record must update 'yes' to ind, if not 'No'.

please help with this.

thanks in advance

raja

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:52:27 GMT

Hi Raja,

Do it like this. loop the first table and read the second table.

Use where condition to satisfy the conditions.

In your select query use joins. Like this.

SELECT mara~matnr

marc~werks

INTO TABLE t_material

FROM mara AS mara INNER JOIN marc AS marc

ON mara_{matnr = marc}matnr

WHERE mara~mtart = p_mtart.

Instead of MARA and MARC here use your tables.

Much Regards,

Amuktha.

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:53:01 GMT

sort tab2 by regid regidno

Loop at tab1.

read table tab2 with key regid regidno binary search.

if sy-subrc eq 0.

ind = yes

else.

ind = no.

endif.

endloop

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:55:34 GMT

Hi,

Get the data from table1 into iTAB1 & table2 into iTAB2.

Loop at itab1.
l_tabix = sy-tabix.
READ table itab2 with key regid = itab1-regid 
                                       regno =itab1-regno.
IF sy-subrc EQ 0.
itab1-ind = 'YES'.
else.
itab1-ind = 'NO' .
ENDif.

MODIFY ITAB1 INDEX l_tabix.
ENDLOOP.

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:56:30 GMT

USE

loop at first table.

READ TABLE secondtable WITH KEY regno = firsttable -regno binary search.

if sy-subrc = 0 .

ids = 'yes'.

else.

ids = 'No'.

endif.

endloop.

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 08:59:41 GMT


SORT table1 BY regid regno.
SORT table2 BY regid regno.

LOOP AT table1 ASSIGNING <fs1>.

READ TABLE table2
WITH KEY regid =  <fs1>-regid
regno =  <fs1>-regno
BINARY SEARCH.

IF sy-subrc EQ 0.
  MOVE 'X' TO <fs1>-ind.
ENDIF.

ENDLOOP.

Re: how to write select statement between two tables

Former Member — Thu, 26 Feb 2009 09:02:28 GMT

Try this,

sort itab1 with key regid regno.

sort itab2 with key regid regno.

Loop at itab1.

read table itab2 with key regid = itab1-regid

regno = itab1-regno binary search.

if sy-subrc eq 0.

itab1-ind = 'YES'

else.

itab-ind = 'NO'.

endif.

endloop.

Regards,

Joan

Re: how to write select statement between two tables

former_member632729 — Thu, 26 Feb 2009 09:08:00 GMT

Hi,


SORT table1 BY regid regno.
SORT table2 BY regid regno.
 
LOOP AT table1 into wa_table1.
 
READ TABLE table2 WITH KEY regid =  wa_table1-regid
                                                regno =  wa_table1-regno   BINARY SEARCH.
 
IF sy-subrc EQ 0.
  MOVE 'YES' TO wa_table1-ind.
  modify table1 from wa_table1 .
else.
  MOVE 'NO' TO wa_table1-ind.
  modify table1 from wa_table1 .
ENDIF.
ENDLOOP.

Re: how to write select statement between two tables

former_member222860 — Thu, 26 Feb 2009 09:08:20 GMT

Use this:

Loop at itab1.
READ table itab2 with key regid = itab1-regid regno =itab1-regno.
IF sy-subrc = 0.
itab1-ind = 'YES'.
else.
itab1-ind = 'NO' .
ENDif.

MODIFY table1 from table itab1.
ENDLOOP.