topic Re: Select data from one table which are not in another table in Application Development and Automation Discussions

Select data from one table which are not in another table

Former Member — Thu, 14 Oct 2010 03:45:06 GMT

Hi All,

I need to select onlyl those MBLNR from MKPF

Which MBLNR are not in BKPF.

I have write code like below:


DATA : BEGIN OF T_BKPF OCCURS 0,
              BELNR LIKE BKPF-BELNR,
       END OF T_BKPF.

DATA: BEGIN OF T_MKPF OCCURS 0,
             MBLNR LIKE MKPF-MBLNR,
        END OF T_MKPF.

SELECT BELNR INTO CORRESPONDING FIELDS OF TABLE T_BKPF.

SELECT MBLNR INTO CORRESPOMDING FIELDS OF TABLE T_MKPF
             WHERE MBLNR NOT IN T_BKPF.

But its not correct. Please tell me how can I get the data.

with thanks,

Moshior

Re: Select data from one table which are not in another table

Former Member — Thu, 14 Oct 2010 03:56:51 GMT

range : r_xblnr for bkpf-xblnr.

SELECT XBLNR from bkpf and populate into range r_xblnr.

SELECT MBLNR INTO CORRESPOMDING FIELDS OF TABLE T_MKPF

WHERE MBLNR NOT IN r_xblnr.

Edited by: Sachin Patil on Oct 14, 2010 9:30 AM

Re: Select data from one table which are not in another table

former_member186741 — Thu, 14 Oct 2010 04:36:02 GMT

this might not be very efficient but you could use a sub-query in the select:

data t type table of mkpf.

select * from mkpf

into table t

where not exists

( select * from bkpf

where belnr = mkpf~mblnr )

Re: Select data from one table which are not in another table

Former Member — Thu, 14 Oct 2010 04:50:08 GMT

Hi Neil,

Thanks..........Problem is solved

Re: Select data from one table which are not in another table

Former Member — Thu, 14 Oct 2010 04:55:24 GMT

Hi,

I'm not sure that your data selection makes sense. Normally MKPF-MBLNR and BKPF-BELNR are completely different document types.

But if it is true, then please verify, whether you have to check for the year MKPF-MJAHR vs. BKPF-GJAHR, too.

Those document numbers in both tables are not unique by there numbers!

Regards,

Klaus

Re: Select data from one table which are not in another table

Former Member — Thu, 14 Oct 2010 07:19:39 GMT

Hi Friend,