topic Re: nested select qurey to convert into normal in Application Development and Automation Discussions

nested select qurey to convert into normal

jayakummar — Fri, 25 Jan 2008 04:42:31 GMT

hai experts,

how to improve the performance to modify the below select qurey converted in to normal?

LOOP AT IQAMB.

SELECT * FROM QAMB WHERE PRUEFLOS EQ IQAMB-PRUEFLOS AND

MBLNR NE ITAB-MBLNR.

SELECT SINGLE MENGE INTO (MSEG-MENGE)

FROM MSEG WHERE MBLNR EQ QAMB-MBLNR AND

MJAHR EQ QAMB-MJAHR AND

ZEILE EQ QAMB-ZEILE AND

BWART EQ '321' AND

UMLGO EQ '1400' AND

XAUTO EQ ' '.

IF SY-SUBRC NE 0. CONTINUE. ENDIF.

MOVE MSEG-MENGE TO TMP_MENGE.

MOVE QAMB-MBLNR TO TMP_MBLNR.

MOVE QAMB-MJAHR TO TMP-MJAHR.

PERFORM ADDTOITAB.

ENDSELECT.

ENDLOOP.

Reward will avail for useful answer.

thanks in adv.

jai.m

Re: nested select qurey to convert into normal

sachin_mathapati — Fri, 25 Jan 2008 05:31:32 GMT

HI jaya kumar,

Always try to avoid using select statement within loop , as this leads to accessing the database for every loop and hence decrease the performance.

You can refer the below code.

If not iqamb is initail.

select * from QAMB into tb_qamb for all entries in iqamb where PRUEFLOS EQ IQAMB-PRUEFLOS .

Endif.

If not tb_qamb is initial.

select * from mseg into tb_mseg for all entries in tb_qamb where MJAHR EQ QAMB-MJAHR .

endif.

loop at i qamb.

Read table tb_qamb into wa_qamb where mblnr eq itab-mblnr.

read table tb_mseg into wa_mseg where MJAHR EQ wa_QAMB-MJAHR AND

ZEILE EQ wa_QAMB-ZEILE AND

BWART EQ '321' AND

UMLGO EQ '1400' AND

XAUTO EQ ' '.

if sy-subrc eq 0.

update fields.

endif.

endloop

Reward points if helpful.

Regards,

Sachin M M

Re: nested select qurey to convert into normal

Former Member — Fri, 25 Jan 2008 13:57:34 GMT

Hi,

Instead of fetching all the field data in QAMB fetch only required fields like in

QAMB table u need only 3 fields MBLNR MJAHR ZEILE so that it will not waste time in fetching all fields .

Try following code .

In declaration declare one Internal tabletb_qamb which contain MBLNR MJAHR ZEILE from QAMB.

If not iqamb is initial.

select MBLNR MJAHR ZEILE from QAMB into tb_qamb for all entries in iqamb where PRUEFLOS EQ IQAMB-PRUEFLOS .

If not tb_qamb is initial.

select single MENGE from mseg into l_menge

WHERE MBLNR EQ QAMB- MBLNR

AND MJAHR EQ QAMB-MJAHR

AND ZEILE EQ QAMB-ZEILE

AND BWART EQ '321'

AND UMLGO EQ '1400'

AND XAUTO EQ ' '.

IF SY-SUBRC NE 0. CONTINUE. ENDIF.

MOVE l_MENGE TO TMP_MENGE.

MOVE tb_QAMB-MBLNR TO TMP_MBLNR.

MOVE tb_QAMB-MJAHR TO TMP-MJAHR.

PERFORM ADDTOITAB.

ENDIF.

ENDSELECT.

ENDIF.

Give reward points if it helps...............

Best Luck

Re: nested select qurey to convert into normal

Former Member — Fri, 25 Jan 2008 14:50:00 GMT

Try using the following piece of code.

TYPES: BEGIN OF ty_qamb,

prueflos TYPE qamb-prueflos,

zaehler TYPE qamb-zaehler ,

mblnr TYPE qamb-mblnr ,

mjahr TYPE qamb-mjahr ,

zeile TYPE qamb-zeile ,

END OF ty_qamb,

BEGIN OF ty_mseg,

mblnr TYPE mseg-mblnr,

mjahr TYPE mseg-mjahr,

zeile TYPE mseg-zeile,

menge TYPE mseg-menge,

END OF ty_mseg.

DATA: w_qamb TYPE ty_qamb,

w_mseg TYPE ty_mseg,

t_qamb TYPE SORTED TABLE OF ty_qamb

WITH NON-UNIQUE KEY prueflos,

t_qamb_tmp TYPE TABLE OF ty_qamb,

t_mseg TYPE HASHED TABLE OF ty_mseg

WITH UNIQUE KEY mblnr mjahr zeile,

i_qamb_tmp LIKE TABLE OF iqamb .

IF NOT iqamb[] IS INITIAL.

i_qamb_tmp[] = iqamb[].

SORT i_qamb_tmp BY prueflos.

DELETE ADJACENT DUPLICATES FROM i_qamb_tmp COMPARING prueflos.

SELECT prueflos

zaehler

mblnr

mjahr

zeile

FROM qamb

INTO TABLE t_qamb

FOR ALL ENTRIES IN i_qamb_tmp

WHERE prueflos EQ i_qamb_tmp-prueflos.

IF sy-subrc EQ 0.

DELETE t_qamb WHERE mblnr NE itab-mblnr.

IF NOT t_qamb[] IS INITIAL.

t_qamb_tmp[] = t_qamb[].

SORT t_qamb_tmp BY mblnr mjahr zeile.

DELETE ADJACENT DUPLICATES FROM t_qamb_tmp

COMPARING mblnr mjahr zeile.

SELECT mblnr

mjahr

zeile

menge

FROM mseg

INTO TABLE t_mseg

FOR ALL ENTRIES IN t_qamb_tmp

WHERE mblnr EQ t_qamb_tmp-mblnr

AND mjahr EQ t_qamb_tmp-mjahr

AND zeile EQ t_qamb_tmp-zeile.

ENDIF.

LOOP AT iqamb.

LOOP AT t_qamb INTO w_qamb

WHERE prueflos EQ iqamb-prueflos.

READ TABLE t_mseg INTO w_mseg

WITH KEY mblnr = w_qamb-mblnr

mjahr = w_qamb-mjahr

zeile = w_qamb-zeile

TRANSPORTING

menge.

CHECK sy-subrc EQ 0.

tmp_menge = w_mseg-menge.

tmp_mblnr = w_qamb-mblnr.

tmp_mjahr = w_qamb-mjahr.

PERFORM addtoitab.

ENDLOOP.

ENDIF.