topic Re: Optimization for this two select- into one select-statement in Application Development and Automation Discussions

Optimization for this two select- into one select-statement

former_member183924 — Wed, 24 Jan 2007 07:18:30 GMT

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

Re: Optimization for this two select- into one select-statement

Former Member — Wed, 24 Jan 2007 07:22:50 GMT

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

Re: Optimization for this two select- into one select-statement

Former Member — Wed, 24 Jan 2007 07:25:30 GMT

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

Re: Optimization for this two select- into one select-statement

former_member404244 — Wed, 24 Jan 2007 07:26:28 GMT

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

Re: Optimization for this two select- into one select-statement

Former Member — Wed, 24 Jan 2007 07:27:31 GMT

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

Re: Optimization for this two select- into one select-statement

Former Member — Wed, 24 Jan 2007 07:29:04 GMT

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.

Re: Optimization for this two select- into one select-statement

former_member183924 — Wed, 24 Jan 2007 08:04:10 GMT

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

Re: Optimization for this two select- into one select-statement

former_member183924 — Wed, 24 Jan 2007 08:19:16 GMT

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

Re: Optimization for this two select- into one select-statement

Former Member — Wed, 24 Jan 2007 08:24:50 GMT

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

Re: Optimization for this two select- into one select-statement

former_member183924 — Wed, 24 Jan 2007 08:39:24 GMT

great this works

regards.

Steffen