select statement

[Former Member]

hi

i need to fetch lifnr bukrs and erdat from LFB1 table

following is the select query

select lifnr

bukrs

erdat

from lfb1

into table gt_lfb1

where lifnr in s_lifnr

and bukrs in s_bukrs.

whereas lfb1-lifnr fetched should not be in BSIK and BSAK table.

selection screen fields are

LFB1-LIFNR

LFB1-BUKRS

how can i fetch those lifnrs

please its urgent.

[Former Member]

SELECT a_{lifnr a}bukrs a~erdat

INTO TABLE <internal table>

FROM lfb1 as A LEFT OUTER JOIN bsik as B

ON A_{lifnr = B}lifnr and A_{bukrs = B}bukrs

left outer join bsak as c

ON A_{lifnr = c}lifnr and A_{bukrs = c}bukrs

where lifnr in s_lifnr

and bukrs in s_bukrs.

madhavi

[Former Member]

I take it you are not aware of the use of the LEFT OUTER JOIN statement.

[Former Member]

you can write exclude .

select ,,,,,

from bsak

for all entries in gt_lfb1

where lifnr ne gt_lfb1-lifnr

and bukrs ne gt_lfb1-bukrs .

select ,,,,,

from bsik

for all entries in gt_lfb1

where lifnr ne gt_lfb1-lifnr

and bukrs ne gt_lfb1-bukrs .

or declare a range table ,

append all vendors in gt_lfb1 into this range with addition excluding.

loop at gt_lfb1.

r_range-sign ='E'

r_range-option='EQ'

r_range-low = gt_lfb1-vendor .

append r_range.

endloop.

use select query

select ,,,,

from bsik

where lifnr in r_range.

bukrs in r_bukrs.

[Former Member]

Hi ,

Try below given select statement

select lifnr into table it_lifnr from lfb1

where lifnr not in ( select lifnr from bsik ).

Hope it will solve your problem.

Regards,

Naren

[Former Member]

Hi,

You can do as below :

1. Get all the lifnr from BSIK into itab for that company code. (No need to write select statement for BSAK as any posting is done against vendor it will first stored in BSIK, if it closed it will be in BSAK)

2. Use the select statement what you have written.

3. Write the below logic.

loop at gt_lfb1.

read table gt_bsik with key bukrs = gt_lfb1-bukrs

lifnr = gt_lfb1-lifnr.

if sy-subrc eq 0.

delete gt_lfb1.

clear gt_lfb1.

endif.

endloop.

Thanks,

Sriram Ponna.

[Former Member]

Hi,

refer the below sample code.

tables:

lfa1,

lfb1.

data:

begin of x_lfa1 occurs 0,

lifnr like lfa1-lifnr,

bukrs like lfb1-bukrs,

erdat like lfb1-erdat,

end of x_lfa1,

begin of x_lifnr occurs 0,

lifnr like lfa1-lifnr,

bukrs like lfb1-bukrs,

end of x_lifnr.

selection-screen begin of block b1.

select-options:

s_lifnr for lfa1-lifnr,

s_bukrs for lfb1-bukrs.

selection-screen end of block b1.

select lifnr

bukrs

erdat from lfb1 into table x_lfa1

where lifnr in s_lifnr

and bukrs in s_bukrs.

if sy-subrc = 0.

sort x_lfa1 by bukrs lifnr.

select lifnr

bukrs from bsak into table x_lifnr

for all entries in x_lfa1

where bukrs = x_lfa1-bukrs

and lifnr = x_lfa1-lifnr.

if sy-subrc = 0.

perform f_delete.

if not x_lfa1[] is initial.

select lifnr

bukrs from bsik into table x_lifnr

for all entries in x_lfa1

where bukrs = x_lfa1-bukrs

and lifnr = x_lfa1-lifnr.

if sy-subrc = 0.

perform f_delete.

endif.

endif.

endif.

endif.

loop at x_lfa1.

write:/ x_lfa1-lifnr.

clear x_lfa1.

endloop.

form f_delete.

sort x_lfa1 by lifnr bukrs.

loop at x_lifnr.

read table x_lfa1 with key lifnr = x_lifnr-lifnr

bukrs = x_lifnr-bukrs

binary search.

if sy-subrc = 0.

delete table x_lfa1.

endif.

clear: x_lifnr,

x_lfa1.

endloop.

refresh x_lifnr.

endform. " f_delete

Reward if useful.

Thanks,

Sreeram.