topic Re: Fetching data form database tables in Application Development and Automation Discussions

Fetching data form database tables

Former Member — Wed, 24 Oct 2007 13:08:08 GMT

Hi all,

i have a requirment as below:

i have to fetch EINA-LIFNR, EINE-EKORG, MARA-MTART, EINA-IDNLF,

EINA-MATNR(also there in MARA), MARA-LVORM, EINA-LOEKZ.

for a given vendor LFA1-LIFNR.

we should not use joins in this.

is there any other way other than FOR ALL ENTRIES.

please help me with code in any of the way, it is very urgent...

thanks,

Rama

Re: Fetching data form database tables

Former Member — Wed, 24 Oct 2007 13:13:12 GMT

write the select query as below

Select EINA_{LIFNR EINE}EKORG EINA_{IDNLF EINA}LOEKZ

EINA_{MATNR MARA}MTART MARA~LVORM

into table ITAB

from EINA as EINA JOIN MARA as MARA

on EINA_{matnr = MARA}MATNR

where EINA~LIFNR IN S_LIFNR.

Regards

ANJI

Message was edited by:

Anji Reddy Vangala

Re: Fetching data form database tables

Former Member — Wed, 24 Oct 2007 13:20:44 GMT

Hi Rama,

Try this.

Select A_{LIFNR A}IDNLF A~LOEKZ

A_{MATNR B}MTART B~LVORM

into table ITAB

from EINA as A JOIN MARA as B

on A_{matnr = B}MATNR

where EINA~LIFNR IN S_LIFNR.

If sy-subrc eq 0.

select EKORG into table i_EKORG

from eine

for all entries in itab.

If sy-subrc ne 0.

*error.

endif.

Reward If Useful.

Regards,

Chitra

Re: Fetching data form database tables

Former Member — Wed, 24 Oct 2007 13:34:21 GMT

Hi Anji & Chitra,

thanks for replay...

but in my requirment i must use only for all entries only.

Thanks,

Rama

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 06:54:19 GMT

Hi Rama,

Use FOR ALL ENTRIES only in your quey.

Regards,

Shilpi

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 07:10:24 GMT

Hi Rama,

Try this.

select lifnr idnlf loekz matnr into table itab

from eina

where lifnr in s_lifnr.

If sy-subrc ne 0.

endif.

select matnr mtart lvorm into table itab2

from matnr

for all entries in itab

where matnr = itab-matnr.

if sy-subrc eq 0.

select EKORG into table i_EKORG

from eine

for all entries in itab1.

If sy-subrc ne 0.

*error.

endif.

Reward If Useful.

Regards,

Chitra

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 07:18:14 GMT

Hi Rama,

Select single LIFNR from LFA1 into table it_lfa1.

Select MATNR

MTART

MFRNR

LVORM

from MARA into corresponding fields of table it_mara

for all entries in it_lfa1

where mfrnr = it_lfa1-lifnr.

Select LIFNR

EKORG

IDNLF

MATNR

LOEKZ

from EINA into corresponding fields of table it_eina

for all entries in it_mara

where lifnr = it_mara-mfrnr

and matnr = it_mara-matnr.

I think this could help you to some extent.

Reward if it is helpful.

Thanks and Regards,

Krithika

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 07:30:27 GMT

Hi Rama,

Please use the following code for your required Query with FOR ALL ENTRIES:-

tables: EINA, EINE , MARA, LFA1.

TYPES: BEGIN OF GS_DATA1,

LIFNR TYPE LFA1-LIFNR,

END OF GS_DATA1,

BEGIN OF GS_DATA2,

LIFNR TYPE EINA-LIFNR,

IDNLF TYPE EINA-IDNLF,

MATNR TYPE EINA-MATNR,

LOEKZ TYPE EINA-LOEKZ,

END OF GS_DATA2,

BEGIN OF GS_DATA3,

LIFNR TYPE EINA-LIFNR,

IDNLF TYPE EINA-IDNLF,

MATNR TYPE EINA-MATNR,

LOEKZ TYPE EINA-LOEKZ,

EKORG TYPE EINE-EKORG,

END OF GS_DATA3,

BEGIN OF GS_DATA4,

LIFNR TYPE LFA1-LIFNR,

IDNLF TYPE EINA-IDNLF,

LOEKZ TYPE EINA-LOEKZ,

EKORG TYPE EINE-EKORG,

MATNR TYPE MARA-MATNR,

MTART TYPE MARA-MTART,

LVORM TYPE MARA-LVORM,

END OF GS_DATA4.

DATA: ITAB1 TYPE STANDARD TABLE OF GS_DATA1,

ITAB2 TYPE STANDARD TABLE OF GS_DATA2,

ITAB3 TYPE STANDARD TABLE OF GS_DATA3,

ITAB4 TYPE STANDARD TABLE OF GS_DATA4,

WA1 LIKE LINE OF ITAB1,

WA2 LIKE LINE OF ITAB2,

WA3 LIKE LINE OF ITAB3,

WA4 LIKE LINE OF ITAB4.

START-OF-SELECTION.

SELECT LIFNR FROM LFA1 INTO CORRESPONDING FIELDS OF TABLE ITAB1.

SELECT LIFNR

IDNLF

MATNR

LOEKZ

FROM EINA

INTO CORRESPONDING FIELDS OF TABLE ITAB2

FOR ALL ENTRIES IN ITAB1

WHERE LIFNR = ITAB1-LIFNR.

SELECT LOEKZ

EKORG

FROM EINE

INTO CORRESPONDING FIELDS OF TABLE ITAB3

FOR ALL ENTRIES IN ITAB2

WHERE LOEKZ = ITAB2-LOEKZ.

SORT ITAB2 BY LOEKZ.

LOOP AT ITAB3 INTO WA3.

READ TABLE ITAB2 INTO WA2 WITH KEY LOEKZ = WA3-LOEKZ BINARY SEARCH.

IF SY-SUBRC = 0.

WA3-IDNLF = WA2-IDNLF.

WA3-MATNR = WA2-MATNR.

WA3-LIFNR = WA2-LIFNR.

MODIFY ITAB3 FROM WA3 TRANSPORTING IDNLF MATNR LIFNR.

ENDIF.

ENDLOOP.

SELECT MATNR

MTART

LVORM

FROM MARA

INTO CORRESPONDING FIELDS OF TABLE ITAB4

FOR ALL ENTRIES IN ITAB3

WHERE MATNR = ITAB3-MATNR.

SORT ITAB3 BY MATNR.

LOOP AT ITAB4 INTO WA4.

READ TABLE ITAB3 INTO WA3 WITH KEY LOEKZ = WA4-LOEKZ BINARY SEARCH.

IF SY-SUBRC = 0.

WA4-LIFNR = WA3-LIFNR.

WA4-IDNLF = WA3-IDNLF.

WA4-LOEKZ = WA3-LOEKZ.

WA4-EKORG = WA4-EKORG.

MODIFY ITAB4 FROM WA4 TRANSPORTING LIFNR IDNLF LOEKZ EKORG.

ENDIF.

ENDLOOP.

Hope this will solve your problem.

Reward points are useful.

Regard,

Shilpi

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 12:56:14 GMT

Hi chitra,

in the bellow code for the last select statement with out where condition its giving syntatical error.

please go through it n suggest me if any other atlernative for this

select lifnr idnlf loekz matnr into table itab

from eina

where lifnr in s_lifnr.

If sy-subrc ne 0.

endif.

select matnr mtart lvorm into table itab2

from matnr

for all entries in itab

where matnr = itab-matnr.

if sy-subrc eq 0.

select EKORG into table i_EKORG

from eine

for all entries in itab1.

If sy-subrc ne 0.

*error.

endif.

Thanks,

Rama

Re: Fetching data form database tables

Former Member — Thu, 25 Oct 2007 13:02:22 GMT

Hi Krithika,

in the code given by u, there is no EKORG field in EINA it is available in EINE table. please go with the code and help me.

Select single LIFNR from LFA1 into table it_lfa1.

Select MATNR

MTART

MFRNR

LVORM

from MARA into corresponding fields of table it_mara

for all entries in it_lfa1

where mfrnr = it_lfa1-lifnr.

Select LIFNR

EKORG

IDNLF

MATNR

LOEKZ

from EINA into corresponding fields of table it_eina

for all entries in it_mara

where lifnr = it_mara-mfrnr

and matnr = it_mara-matnr.

Thanks,

Rama