topic Re: Regarding select statement in Application Development and Automation Discussions

Regarding select statement

Former Member — Mon, 24 Dec 2007 13:45:29 GMT

Hi,

I have two tables LFA1 and EKKO,

from LFA1 I fetch LIFNR and NAME1

from EKKO I have to fetch EBELN basing on lfa1-lifnr,

here for one lifnr I have 4 EBELN.

MY C0DE

types: begin of t_lifnr,

lifnr type lfa1-lifnr,

name1 type lfa1-name1,

end of t_lifnr,

begin of t_ekko,

lifnr type ekko-lifne,

ebeln type ekko-ebeln,

end of t_ekko,

begin of t_final,

lifnr type lfa1-lifnr,

name1 type lfa1-name1,

ebeln type ekko-ebeln,

end of t_final.

data: itab type standard table of t_lfa1,

w_itab type t_lfa1,

jtab type standard table of t_ekko,

w_jtab type t_ekko,

i_final type standard table of t_final,

w_final type t_final

select lifnr name1 into table itab from lfa1.

if not itab is initial.

select lifnr ebeln into table jtab

for all entries in itab

where lifnr = itab-lifnr.

loop at itab into w_itab.

w_final-lifnr = w_itab-lifnr.

w_final-name1 = w_itab-name1.

read table jtab into w_jatb with key lifnr = w_itab-lifnr.

w_final-ebeln = w_jtab-ebeln.

enloop.

loop at i_final into w_final.

write:/ w_final-lifnr,w_final-name1,w_final-ebeln.

enloop.

here for lifnr = 1

I have 4 ebeln but after display of write statement I can see only one ebeln for lifnr = 1, but actually it has to be 4

so how can I overcome this problem.

my output should be like this

lifnr name1 ebeln

1 abc 101

102

103

104

but I see it as

lifnr name1 ebeln

1 abc 101

Edited by: shilpa shilpa on Dec 24, 2007 2:45 PM

Edited by: shilpa shilpa on Dec 24, 2007 2:48 PM

Re: Regarding select statement

Former Member — Mon, 24 Dec 2007 13:54:11 GMT

HI Shilpa,

In case of using read table jtab, use loop at jtab. because u have multiple data.

loop at itab into w_itab.

w_final-lifnr = w_itab-lifnr.

w_final-name1 = w_itab-name1.

********read table jtab into w_jatb with key lifnr = w_itab-lifnr.

loop at jtab into w_jtab.

w_final-ebeln = w_jtab-ebeln.

append w_final into i_final.

endloop.

output will come this way.

1 abc 101

1 abc 102

1 abc 103

1 abc 104.

Reward if useful

Regards

ANUPAM

Re: Regarding select statement

former_member194152 — Mon, 24 Dec 2007 13:54:27 GMT

Hi,

You are using read table statement inside the loop and use that statment only when u know the key field of that table here what happen for each lifnr in jtab there are more then 1 entries found and hence system gets confused to assign particular value so what u can do just convert the loop because lifnr is primary key for lfa1 so u can use read table statement for itab but not for jtab.

Please rewards if helpful.

Regards

Gagan

Re: Regarding select statement

Former Member — Mon, 24 Dec 2007 14:04:07 GMT

Hi Shilpa,

In the existing code,

pls modify like this.

types: begin of t_lifnr,

lifnr type lfa1-lifnr,

name1 type lfa1-name1,

end of t_lifnr,

begin of t_ekko,

lifnr type ekko-lifne,

ebeln type ekko-ebeln,

end of t_ekko,

begin of t_final,

lifnr type lfa1-lifnr,

name1 type lfa1-name1,

ebeln type ekko-ebeln,

end of t_final.

data: itab type standard table of t_lfa1,

w_itab type t_lfa1,

jtab type standard table of t_ekko,

w_jtab type t_ekko,

i_final type standard table of t_final,

w_final type t_final.

ranges: ra_lifnr for lfa1-lifnr.

select lifnr name1 into table itab from lfa1.

if not itab[] is initial.

loop at itab into w_itab.

ra_lifnr-sign = 'I'.

ra_lifnr-option = 'EQ'.

ra_lifnr-low = w_itab-lifnr.

append ra_lifnr.

endloop.

endif.

if not itab is initial.

select lifnr ebeln into table jtab

--*for all entries in itab --delete this code

where lifnr in RA_lifnr.

loop at itab into w_itab.

w_final-lifnr = w_itab-lifnr.

w_final-name1 = w_itab-name1.

read table jtab into w_jatb with key lifnr = w_itab-lifnr.

w_final-ebeln = w_jtab-ebeln.

append w_final to i_final .

enloop.

loop at i_final into w_final.

write:/ w_final-lifnr,w_final-name1,w_final-ebeln.

enloop.

Now u try and let me know.

Thanks and regards,

- Selvapandian Arunachalam

Re: Regarding select statement

Former Member — Mon, 24 Dec 2007 14:06:38 GMT

hi,

Please try with this code.You will get result as you wish.

DATA: BEGIN OF t_lfa1 OCCURS 0,

lifnr TYPE lfa1-lifnr,

name1 TYPE lfa1-name1,

END OF t_lfa1.

DATA:BEGIN OF t_final OCCURS 0,

lifnr TYPE lfa1-lifnr,

name1 TYPE lfa1-name1,

ebeln TYPE ekko-ebeln,

END OF t_final.

SELECT lifnr name1 INTO TABLE t_lfa1 FROM lfa1.

IF NOT t_lfa1[] IS INITIAL.

SELECT lifnr ebeln INTO CORRESPONDING FIELDS OF TABLE t_final

FROM ekko

FOR ALL ENTRIES IN t_lfa1

WHERE lifnr = t_lfa1-lifnr.

ENDIF.

LOOP AT t_final.

READ TABLE t_lfa1 WITH KEY lifnr = t_final-lifnr.

IF sy-subrc = 0.

t_final-name1 = t_lfa1-name1.

MODIFY t_final TRANSPORTING name1.CLEAR t_final.

ENDIF.

ENDLOOP.

LOOP AT t_final .

WRITE:/ t_final-lifnr,t_final-name1,t_final-ebeln.

ENDLOOP.

Regds

Parvathi

Please reward points if helpful.......................

Re: Regarding select statement

Former Member — Mon, 24 Dec 2007 14:07:29 GMT

good

you have missed the APPEND statement in all your select query, and that is the reason it is displaying only one record,

check

select lifnr ebeln into table jtab

for all entries in itab

where lifnr = itab-lifnr.

this select statement in debug mode and use the APPEND statement appropriately.

thanks

mrutyun^

Re: Regarding select statement

Former Member — Mon, 24 Dec 2007 14:09:09 GMT

This code is Final .

REPORT ZSDN.

TYPES: BEGIN OF T_LIFNR,

LIFNR TYPE LFA1-LIFNR,

NAME1 TYPE LFA1-NAME1,

END OF T_LIFNR,

BEGIN OF T_EKKO,

LIFNR TYPE EKKO-LIFNR,

EBELN TYPE EKKO-EBELN,

END OF T_EKKO,

BEGIN OF T_FINAL,

LIFNR TYPE LFA1-LIFNR,

NAME1 TYPE LFA1-NAME1,

EBELN TYPE EKKO-EBELN,

END OF T_FINAL.

DATA: ITAB TYPE STANDARD TABLE OF T_LIFNR,

W_ITAB TYPE T_LIFNR,

JTAB TYPE STANDARD TABLE OF T_EKKO,

W_JTAB TYPE T_EKKO,

I_FINAL TYPE STANDARD TABLE OF T_FINAL,

W_FINAL TYPE T_FINAL.

RANGES: RA_LIFNR FOR LFA1-LIFNR.

START-OF-SELECTION.

SELECT LIFNR NAME1 INTO TABLE ITAB FROM LFA1.

IF NOT ITAB[] IS INITIAL.

LOOP AT ITAB INTO W_ITAB.

RA_LIFNR-SIGN = 'I'.

RA_LIFNR-OPTION = 'EQ'.

RA_LIFNR-LOW = W_ITAB-LIFNR.

APPEND RA_LIFNR.

ENDLOOP.

ENDIF.

IF NOT ITAB[] IS INITIAL.

SELECT LIFNR EBELN INTO TABLE JTAB

FROM EKKO

WHERE LIFNR IN RA_LIFNR.

ENDIF.

LOOP AT ITAB INTO W_ITAB.

W_FINAL-LIFNR = W_ITAB-LIFNR.

W_FINAL-NAME1 = W_ITAB-NAME1.

READ TABLE JTAB INTO W_JTAB WITH KEY LIFNR = W_ITAB-LIFNR.

IF SY-SUBRC = 0.

W_FINAL-EBELN = W_JTAB-EBELN.

ENDIF.

APPEND W_FINAL TO I_FINAL.

ENDLOOP.

LOOP AT I_FINAL INTO W_FINAL.

WRITE:/ W_FINAL-LIFNR,W_FINAL-NAME1,W_FINAL-EBELN.

ENDLOOP.