Optimization for this two select- into one select-statement

[former_member183924]

Hi experts,

I realised my content in the it_mat and it_mat_info with these two Select-statements. But I think it is not very effective, because I select most of the data twice.

 SELECT DISTINCT rkwa~matnr makt~maktx
      FROM rkwa INNER JOIN makt ON rkwa~matnr = makt~matnr
      INTO CORRESPONDING FIELDS OF TABLE it_mat
      WHERE rkwa~werks =  wa_info-werks
      AND   rkwa~lifnr =  lieferant_nr
      AND   rkwa~budat >= wa_info-buvon
      AND   rkwa~budat <= wa_info-bubis
      AND   rkwa~sobkz = 'K'
      AND   makt~spras = 'D'
      .


      SELECT *
      FROM rkwa INNER JOIN makt ON rkwa~matnr = makt~matnr
      INTO CORRESPONDING FIELDS OF TABLE it_mat_info
      WHERE rkwa~werks =  wa_info-werks
      AND   rkwa~lifnr =  lieferant_nr
      AND   rkwa~budat >= wa_info-buvon
      AND   rkwa~budat <= wa_info-bubis
      AND   rkwa~sobkz = 'K'
      AND   makt~spras = 'D'
     .

In it_mat I only need the possible values of materials. I have to do this with "distinct" because in the next step (it is a BSP Application) I want to do a LOOP over the it_mat so that I get an nice order.

It is possible to take the second SELECT statement and capture the occur materials into the it_mat? I already tried something with the READ statement and to LOOP over the "it_mat_info" but with no result.

thanks, regards

Steffen

[Former Member]

Hi

select all the required fields from the table and then delete adjacent duplicates from the internal table and move it to another itab.

this will be very fast.

Thanks

Shiva

[Former Member]

Hello

Create the 2nd select with the FOR ALL ENTRIES IN itab statement

SELECT *

FROM rkwa INNER JOIN makt ON rkwa_{matnr = makt}matnr

INTO CORRESPONDING FIELDS OF TABLE it_mat_info

FOR ALL ENTRIES IN it_mat.

Hope this helps.

Gabriel

[former_member404244]

Hi,

SELECT *

FROM rkwa INNER JOIN makt ON rkwa_{matnr = makt}matnr

INTO CORRESPONDING FIELDS OF TABLE it_mat_info

WHERE rkwa~werks = wa_info-werks

AND rkwa~lifnr = lieferant_nr

AND rkwa~budat >= wa_info-buvon

AND rkwa~budat <= wa_info-bubis

AND rkwa~sobkz = 'K'

AND makt~spras = 'D'

now sort the internal table it_mat_info by matnr.

delete adjacent duplicates from it_mat_info comparing matnr.

try like this..

Regards,

nagaraj

[Former Member]

Hi Steffen,

You can use the

- Select to populate the internal table it_mat_info.

- Sort it by MATNR.

- Loop at it_mat_info and use AT NEW matnr and write append it_mat_info to it_mat.

Refer the code below -

sort it_mat_info by matnr.

loop at it_mat_info.

move it_mat_info to wa_mat_info.

at new matnr.

it_mat-matnr = wa_mat_info-matnr.

it_mat-maktx = wa_mat_info-maktx.

append it_mat.

clear it_mat_info.

endat.

endloop.

Hope this helps.

Reward points if found useful...!

Cheers

Abhishek

[Former Member]

you can do it using one select statement.

SELECT rkwa_{matnr rkwa}field1 rkwa_{field2 makt}maktx

FROM rkwa INNER JOIN makt ON rkwa_{matnr = makt}matnr

INTO CORRESPONDING FIELDS OF TABLE it_mat

WHERE rkwa~werks = wa_info-werks

AND rkwa~lifnr = lieferant_nr

AND rkwa~budat >= wa_info-buvon

AND rkwa~budat <= wa_info-bubis

AND rkwa~sobkz = 'K'

AND makt~spras = 'D'.

it_mat_temp[] = it_mat[].

sort it_mat_temp by matnr.

delete adjacent duplicates from it_mat_temp comparing matnr.

[former_member183924]

OK, thanks for the extreme fast replies, most of you had the same idea.

I tried it with

it_mat[] = it_mat_info[].
SORT it_mat BY matnr.
DELETE ADJACENT DUPLICATES FROM it_mat COMPARING matnr.

But then I get not the exact material number. The it_mat_info in which I do my complete selection has got the structure of:

mblnr TYPE rkwa-mblnr,
        zeile TYPE rkwa-zeile,
        belnr TYPE rkwa-belnr,
        matnr TYPE rkwa-matnr,
        maktx TYPE makt-maktx

The it_mat has this structure:

matnr TYPE rkwa-matnr,
        maktx TYPE makt-maktx

So by doing : "it_mat = it_mat_info" I get the first two fields of it_mat_info (mblnr and zeile) into it_mat_info-matnr. So how can I refer: it_mat-matnr = it_mat_info-matnr (like I wrote it here it is of course not possible in OO I think)???

the LOOP with "AT NEW " is very cool I can use this at another place in application!

regards,

Steffen

[Former Member]

loop at it_mat_info into wamatinfo.

move-corresponding wamatinfo to wamat.

append wamat to it_mat.

endloop.

now

SORT it_mat BY matnr.

DELETE ADJACENT DUPLICATES FROM it_mat COMPARING matnr.

it will replace the corresponding values.

regards

shiba dutta

[former_member183924]

the field didn't transfered correct from one itab to another.

[former_member183924]

great this works

regards.

Steffen