topic Select diff. fields into internal table in Application Development and Automation Discussions

Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:00:27 GMT

Hi Experts....

I have to fetch the data from EKKO EKPO EKET into one internal table.

I dont want to use JOIN due to the performance issue.

I want to put the three select query and fetch all data into a single internal table.

But what i have noted that the data get refreshed as soon as second select query runs.

Below is the code.

1. The join for which i have to put into 3 diff. select query

SELECT b_{ebeln b}ebelp b_{werks c}eindt b_{matnr b}lgort b~menge

b_{meins a}lifnr c_{menge c}wemng

INTO TABLE int_tab_sc

FROM ekko AS a INNER JOIN ekpo AS b

ON a_{ebeln = b}ebeln

INNER JOIN eket AS c

ON b_{ebeln = c}ebeln

AND b_{ebelp = c}ebelp

WHERE a~bedat IN s_bedat

AND a~aedat IN s_aedat

AND a~bsart EQ p_bsart

AND a~lifnr IN s_lifnr

AND a~ebeln IN s_ebeln

AND b~matnr IN s_matnr

AND b~werks = p_werks

AND b~pstyp = '3'

AND ( b~elikz = space " Delivery not Complete

OR b~uebtk = 'X' ) " Unlimited marked

AND c~etenr = '0001'

AND a~loekz = space

AND b~loekz = space.

2. The 3 select query which I have written, but the data in the final internal tab is only have the last select query data.

select ebeln lifnr from ekko INTO CORRESPONDING FIELDS OF TABLE

int_tab_sc where ebeln IN s_ebeln

AND aedat IN s_aedat

AND bsart EQ p_bsart

AND lifnr IN s_lifnr

AND bedat IN s_bedat

AND loekz = space.

select ebeln ebelp werks matnr lgort menge meins

from ekpo INTO CORRESPONDING FIELDS OF TABLE int_tab_sc FOR ALL ENTRIES

IN int_tab_sc where ebeln = int_tab_sc-ebeln

and matnr IN s_matnr

AND werks = '310'

and pstyp = '3'

AND ( elikz = space

OR uebtk = 'X' )

AND loekz = space.

SELECT eindt menge wemng FROM eket INTO CORRESPONDING FIELDS OF TABLE

int_tab_sc FOR ALL ENTRIES IN int_tab_sc where ebeln = int_tab_sc-ebeln

AND ebelp = int_tab_sc-EBELP

and etenr = '0001'.

Please help me out.

Points will be awarded.

Thank-You.

Regards,

vinsee

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:04:39 GMT


select ebeln lifnr from ekko INTO CORRESPONDING FIELDS OF TABLE
int_tab_sc where ebeln IN s_ebeln

select ebeln ebelp werks matnr lgort menge meins
from ekpo INTO CORRESPONDING FIELDS OF TABLE int_tab_sc FOR ALL ENTRIES

In both cases your saving data into the same IT...<b>int_tab_sc</b> that's why data is cleared...

You must a new IT for second query, and then LOOP it to complete with data from the first IT...

Or just use the INNER JOIN...Providing the right PK you should not get much performance problems....

Otherwise....Build a view -:)

Greetings,

Blag.

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:06:18 GMT

Hi,

You cannot have the same internal table int_tab_sc in all the SQLs..That is the reason it is overwriting as soon as the second sql is executed..

Create new internal tables..And give it the corresponding SQLs..Then use LOOP AT to move the values to the internal table int_tab_sc..

LOOP AT int_ekko.

line items.

LOOP AT int_ekpo WHERE ebeln = int_ekko-ebeln.

Schedule lines.

LOOP AT int_eket WHERE ebeln = int_ekko-ebeln

AND ebelp = int_ekpo-ebelp.

MOVE-CORRESPONDING int_eket TO int_tab_sc.

move the values to the final internal table.

APPEND int_itab_sc.

ENDLOOP.

Thanks,

Naren

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:06:46 GMT

Hi,

With inner join your selection will work <b>much much</b> faster. Please use inner join.

Regards,

Wojciech

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:07:14 GMT

Hi,

When you talk of performance issues with the JOIN statement, then even the statement INTO CORRESPONDING FIELDS OF TABLE is also cause performance issues.

Actually, 3 SELECT statements with INTO CORRESPONDING might cause more performance issues than 1 SELECT with JOIN.

My suggestions to have the JOIN or use/create a view with the 3 tables.

Regards,

Sumant.

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:15:02 GMT

Hi again

Naren what do you think ll the performance improve with

Loop

Req. internal table value assignment

Endloop

I have still doubt.

For the other Experts....

I have used the inner join with the required PK, but belive me its not working. And thats why I have to move for this option.

Although if u have any other solution please send the code......really appriciated.

Thank-You.

Regards,

vinsee

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 20:20:14 GMT

Hi,

If you don't want to use INNER JOIN I believe you have to use

LOOP AT..

ENDLOOP.

For improving performances you can use the parallel cursor technique..

Ex..

SORT INT_EKPO BY EBELN.

LOOP AT INT_EKKO.

READ TABLE INT_EKPO TRANSPORTING NO FIELDS

WITH KEY EBELN = INT_EKKO-EBELN

IF SY-SUBRC = 0.

Store the index .

V_TABIX = SY-TABIX.

LOOP AT INT_EKPO INTO WA.

Exit condition.

IF WA-EBELN <> INT_EKKO-EBELN.

EXIT.

ENDIF.

ENDLOOP.

ENDIF.

ENDLOOP.

Thanks,

Naren

Re: Select diff. fields into internal table

Former Member — Wed, 18 Apr 2007 21:31:48 GMT

Thanks Experts

I got the satisfactory solution.....

here is the solution which i got.....

select ebeln lifnr from ekko INTO CORRESPONDING FIELDS OF TABLE

int_tab_sc1 where ebeln IN s_ebeln

AND aedat IN s_aedat

AND bsart EQ p_bsart

AND lifnr IN s_lifnr

AND bedat IN s_bedat

AND loekz = space.

select ebeln ebelp werks matnr lgort menge meins

from ekpo INTO CORRESPONDING FIELDS OF TABLE int_tab_sc2 FOR ALL ENTRIES

IN int_tab_sc1 where ebeln = int_tab_sc1-ebeln

and matnr IN s_matnr

AND werks = '310'

and pstyp = '3'

AND ( elikz = space

OR uebtk = 'X' )

AND loekz = space.

SELECT ebeln EBELP eindt menge wemng FROM eket INTO CORRESPONDING

FIELDS OF TABLE

int_tab_sc3 FOR ALL ENTRIES IN int_tab_sc2 where ebeln =

int_tab_sc2-ebeln

AND ebelp = int_tab_sc2-EBELP

and etenr = '0001'.

loop at int_tab_sc3.

read table int_tab_sc2 with key ebeln = int_tab_sc3-ebeln

ebelp = int_tab_sc3-ebelp.

IF SY-SUBRC = 0.

READ TABLE INT_TAB_SC1 WITH KEY EBELN = INT_TAB_SC3-EBELN.

IF SY-SUBRC = 0.

INT_TAB_SC-lifnr = INT_TAB_SC1-lifnr.

INT_TAB_SC-ebeln = INT_TAB_SC2-ebeln.

INT_TAB_SC-ebelp = INT_TAB_SC2-ebelp.

INT_TAB_SC-werks = INT_TAB_SC2-werks.

INT_TAB_SC-matnr = INT_TAB_SC2-matnr.

INT_TAB_SC-lgort = INT_TAB_SC2-lgort.

INT_TAB_SC-menge = INT_TAB_SC2-menge.

INT_TAB_SC-meins = INT_TAB_SC2-meins.

INT_TAB_SC-eindt = INT_TAB_SC3-eindt.

Append int_tab_sc.

ENDIF.

endloop.

thanks for ur response....

regards,

vinsee