Join two tables problem

[Former Member]

Hi Experts,

I want to select all rows from table EBAN where the values for MATNR is not in table A512 as on date.

example: Material 1000012 exists in my row in eban.

I have to check if it exists or not in table A512.

If it is missing - I have to display the eban record.

If it is found then I have to check whether the A512 record is valid for todays date (DATAB and DATBI fields. )

Please help me how do I do it using a query rather than selecting twice and then looping and deleting records.

Regards,

Abdullah

[Former Member]

Select * from EBAN into table t_eban where...........

Select * from A512 into table t_a512 where........

Loop at t_eban.

read table t_a521 with key matnr = t_eban-matnr.

if sy-subrc eq 0.

check whether the A512 record is valid for todays date

else.

display the eban record.

endif.

endloop.

[Former Member]

Hi,

Try the following :

select matnr
from A512 into corresponding fields of table g_T_A512
where date =  sy-datum.

select * from EBAN
into table g_t_eban
for all entries in the table g_t_A512
where matnr NE g_t_A512-matnr.

if sy-subrc eq 0.
* Display the EBAN record
else.
* check whether the A512 record is valid for todays date (DATAB and DATBI fields. )
endif.

Regards,

Mansi

[Former Member]

This will require me to loop and delete individual records. Well that was there in my mind. I just wanted to know if I could join the two tables in a way that the query returns me the result rather than deleting records in a loop.

[Former Member]

Hi,

Try this code...

Select a~matnr

from EBAN as a

INTO TABLE ITAB

INNER jOIN ON A512 AS B

WHERE NOT A_{MATNR = B}MATNR.

[ThomasZloch]

You could try a LEFT OUTER JOIN between EBAN and A512. In the WHERE-condition you can then check for any A512 field, if it is blank OR there is no date range covering today's date, then you want the EBAN row in your result. Make sure you use all primary key fields of A512 in the ON-conditions of the join.

Thomas

[Former Member]

Hi,

Could you try to build a range of MATNR at first.

Select records from A512 WHERE matnr IN MATNR and date = todays date

Build a range of matnr from the data selected above ie a512_MATNR.

Select data from EBAN where matnr IN MATNR and MATNR not IN a512_matnr.

Building the ranges table should not be very time consuming.

Do revert if you need anything further on this.