Regadrding select statements

[Former Member]

Hello to all

Here in my requirement i did one select query logic

bt performance wise it is not working proper way

if u find any changes relevant to below select statement plz reply me

here is my Select Query

*

• Extract all the data from the database tables .

• -----------------------------------------------

*

*

• Retrieving data from table MARA .

• -----------------------------------------------

SELECT * FROM mara

INTO CORRESPONDING FIELDS OF TABLE it_mara

WHERE matnr IN s_matnr AND mtart IN s_mtart AND ernam IN s_ernam.

sort it_mara by matnr.

• DELETE ADJACENT DUPLICATES FROM it_mara comparing all fields.

IF sy-subrc NE 0.

MESSAGE s005(zmm).

REFRESH: it_marc.

STOP.

ENDIF.

*

• Retrieving data of Product Group Level3 on the condition that ZZPARENT should not be empty .

• ---------------------------------------------------------------------------------------------

*

IF wa_mara-zzparent NE ' '.

READ TABLE it_mara INTO wa_mara WITH KEY matnr = wa_mara-matnr.

MOVE wa_mara-zzparent TO wa_final-prodh3.

ENDIF.

SELECT matnr werks mtvfp mfrgr ladgr prctr sernp stawn ekgrp dismm lgfsb insmk

FROM marc

INTO CORRESPONDING FIELDS OF TABLE it_marc

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr

AND werks IN s_werks .

sort it_marc by matnr werks.

• DELETE ADJACENT DUPLICATES FROM it_marc.

IF sy-subrc NE 0.

MESSAGE s005(zmm).

REFRESH: it_marc.

STOP.

ENDIF.

*

• Retrieving data from table MVKE .

• ---------------------------------

*

SELECT matnr vkorg vtweg dwerk kondm ktgrm mtpos mvgr1 mvgr2 mvgr3 mvgr4 mvgr5

FROM mvke

INTO CORRESPONDING FIELDS OF TABLE it_mvke

FOR ALL ENTRIES IN IT_MARA

where matnr = it_mara-matnr and

vkorg IN s_vkorg.

IF sy-subrc NE 0.

MESSAGE s005(zmm).

REFRESH: it_marc.

STOP.

ENDIF.

*

• Retrieving data from table MARM, satisfying the condition for Case Pack Factor as CS .

• --------------------------------------------------------------------------------------

*

SELECT MATNR meinh umrez laeng breit hoehe meabm volum voleh brgew

FROM marm

INTO CORRESPONDING FIELDS OF TABLE it_marm

FOR ALL ENTRIES IN IT_MARA

WHERE MATNR = IT_MARA-MATNR

AND meinh = 'CS'.

*

• Retrieving data from table MAKT .

• ---------------------------------

*

SELECT matnr maktx

FROM makt

INTO CORRESPONDING FIELDS OF TABLE it_makt

WHERE matnr IN s_matnr .

*

• Retrieving data from table AMPL and fetching data for field GPCCODE .

• ----------------------------------------------------------------------

*

• SELECT bmatn ematn

• FROM ampl

• INTO CORRESPONDING FIELDS OF TABLE it_ampl

• FOR ALL ENTRIES IN it_mara

• WHERE bmatn = it_mara-matnr

• AND datuv LE sy-datum

• AND datub GE sy-datum.

****

• IF NOT wa_ampl IS INITIAL.

• READ TABLE it_ampl INTO wa_ampl WITH KEY bmatn = wa_mara-matnr.

• MOVE wa_ampl-ematn TO wa_final-gpccode.

• ELSE.

• SELECT bmatn ematn

• FROM ampl

• INTO CORRESPONDING FIELDS OF TABLE it_ampl

• FOR ALL ENTRIES IN it_mara

• WHERE bmatn = it_mara-matnr.

****

• IF NOT wa_ampl IS INITIAL.

• READ TABLE it_ampl INTO wa_ampl WITH KEY bmatn = wa_ampl-bmatn.

• MOVE wa_mara-mfrpn TO wa_final-gpccode.

• ELSE.

• wa_final-gpccode = ' '.

• ENDIF.

• ENDIF.

*

• Moving Relevant data to IT_FINAL (Final Internal Table ) .

• -------------------------------------------------------------

*

LOOP AT it_mara INTO wa_mara .

MOVE-CORRESPONDING wa_mara TO wa_final.

MOVE : wa_mara-zzparent TO wa_final-prodh3.

READ TABLE it_mvke INTO wa_MVKE with key vkorg = wa_mvke-vkorg.

MOVE : wa_mvke-vkorg TO wa_final-vkorg,

wa_mvke-vtweg TO wa_final-vtweg,

wa_mvke-dwerk TO wa_final-dwerk,

wa_mvke-kondm TO wa_final-kondm,

wa_mvke-ktgrm TO wa_final-ktgrm,

wa_mvke-mtpos TO wa_final-mtpos,

wa_mvke-mvgr1 TO wa_final-mvgr1,

wa_mvke-mvgr2 TO wa_final-mvgr2,

wa_mvke-mvgr3 TO wa_final-mvgr3,

wa_mvke-mvgr4 TO wa_final-mvgr4,

wa_mvke-mvgr5 TO wa_final-mvgr5.

• READ TABLE it_marc INTO wa_marc WITH KEY matnr = wa_marc-matnr .

• MOVE : wa_marc-mtvfp TO wa_final-mtvfp,

• wa_marc-mfrgr TO wa_final-mfrgr,

• wa_marc-ladgr TO wa_final-ladgr,

• wa_marc-prctr TO wa_final-prctr,

• wa_marc-sernp TO wa_final-sernp,

• wa_marc-stawn TO wa_final-stawn,

• wa_marc-ekgrp TO wa_final-ekgrp,

• wa_marc-dismm TO wa_final-dismm,

• wa_marc-lgfsb TO wa_final-lgfsb,

• wa_marc-insmk TO wa_final-insmk.

READ TABLE it_ampl INTO wa_ampl WITH KEY bmatn = wa_mara-matnr BINARY SEARCH.

MOVE : wa_ampl-bmatn TO wa_final-bmatn.

READ TABLE it_marm INTO wa_marm WITH KEY matnr = wa_mara-matnr BINARY SEARCH.

MOVE: wa_marm-meinh TO wa_final-meinh,

wa_marm-umrez TO wa_final-umrez,

wa_marm-laeng TO wa_final-laeng,

wa_marm-breit TO wa_final-breit,

wa_marm-hoehe TO wa_final-hoehe,

wa_marm-meabm TO wa_final-meabm,

wa_marm-volum TO wa_final-volum,

wa_marm-brgew TO wa_final-brgew.

READ TABLE it_makt INTO wa_makt WITH KEY matnr = wa_mara-matnr.

MOVE wa_makt-maktx TO wa_final-maktx.

*******************************FOR SALE ORGANIZATION CHECK BOX***************

IF c_sodata = 'X'.

SELECT matnr vkorg lvorm

FROM mvke

INTO CORRESPONDING FIELDS OF TABLE it_checkselected

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr

AND vkorg IN s_vkorg

AND vkorg NE 'YMUS'

AND lvorm EQ ' '.

IF sy-subrc NE 0.

MESSAGE s005(zmm).

STOP.

ENDIF.

LOOP AT it_checkselected INTO wa_checkselected.

READ TABLE it_checkselected INDEX sy-index INTO wa_checkselected .

MOVE : wa_checkselected-vkorg TO wa_final-vkorg,

wa_checkselected-lvorm TO wa_final-lvorm.

ENDLOOP.

ENDIF.

***************************FOR PLANT CHECK BOX*****************************

*

• Retrieving data if check box for Plantdata is Enabled .

• --------------------------------------------------------

*

IF c_pdata = 'X'.

• SELECT matnr werks mtvfp mfrgr ladgr prctr sernp stawn ekgrp dismm lgfsb insmk

• FROM marc

• INTO CORRESPONDING FIELDS OF TABLE it_marc

• FOR ALL ENTRIES IN it_mara

• WHERE matnr = it_mara-matnr

• AND werks IN s_werks .

READ TABLE it_marc INTO wa_marc WITH KEY matnr = wa_marc-matnr BINARY SEARCH.

MOVE : wa_marc-werks TO wa_final-werks,

wa_marc-mtvfp TO wa_final-mtvfp,

wa_marc-mfrgr TO wa_final-mfrgr,

wa_marc-ladgr TO wa_final-ladgr,

wa_marc-prctr TO wa_final-prctr,

wa_marc-sernp TO wa_final-sernp,

wa_marc-stawn TO wa_final-stawn,

wa_marc-ekgrp TO wa_final-ekgrp,

wa_marc-dismm TO wa_final-dismm,

wa_marc-lgfsb TO wa_final-lgfsb,

wa_marc-insmk TO wa_final-insmk.

SELECT werks lgnum

FROM t320

INTO CORRESPONDING FIELDS OF TABLE it_t320

FOR ALL ENTRIES IN it_marc

WHERE werks = it_marc-werks

AND werks IN s_werks.

SELECT matnr lgnum ltkza ltkze lgbkz block lhmg1 lhme1 lety1 zzcase_orient

FROM mlgn

INTO CORRESPONDING FIELDS OF TABLE it_mlgn

FOR ALL ENTRIES IN it_t320

WHERE lgnum = it_t320-lgnum

AND matnr IN s_matnr.

READ TABLE it_mlgn INTO wa_mlgn WITH KEY lgnum = wa_t320-lgnum BINARY SEARCH.

MOVE: wa_mlgn-lgnum TO wa_final-lgnum,

wa_mlgn-ltkza TO wa_final-ltkza,

wa_mlgn-ltkze TO wa_final-ltkze,

wa_mlgn-lgbkz TO wa_final-lgbkz,

wa_mlgn-block TO wa_final-block,

wa_mlgn-lhmg1 TO wa_final-lhmg1,

wa_mlgn-lhme1 TO wa_final-lhme1,

wa_mlgn-lety1 TO wa_final-lety1,

wa_mlgn-zzcase_orient TO wa_final-zzcase_orient.

SELECT matnr bklas

FROM mbew

INTO TABLE it_mbew

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr.

READ TABLE it_mbew INTO wa_mbew WITH KEY matnr = wa_mara-matnr BINARY SEARCH.

MOVE wa_mbew-bklas TO wa_final-bklas.

ENDIF.

APPEND wa_final TO it_final .

ENDLOOP.

DELETE ADJACENT DUPLICATES FROM it_final comparing all fields.

IF sy-subrc NE 0.

MESSAGE s005(zmm).

STOP.

CLEAR sy-subrc.

ENDIF.

REFRESH: it_mara,it_marc,it_mvke,it_mlgn,it_ampl,it_t320,

it_checkselected.

plese help me its an urgent requirement

Points will be rewarded fro satisfying answer

Regards

ASLAM

[Former Member]

select only those fields which u want in ur program, in the first select statement u are selecting all fields, u can avoiid that.

secondly u are using into corresponding fields of table, try to avoid that also

define the IT with fields which u r retrieving from the select statement and use into table, its more efficient that into corresponding fields

try to avoid select inside loop statement.

bye

[Former Member]

Hi,

Get the field that you need from the database table. Please sort the table before deleting adjacent duplicates.

sort itab by fieldname. " If u want to sort based on fieldname otherwise u can just use sort itab.

delete adjacent duplicates from itab comparing fieldname.

Award me points if it helps....

Thanks,

Sheel

[Former Member]

After each select statement check the sy-subrc then delete the duplicates with comparing key fields.

Remove into corresponding fields...

Select only the required fields instead of giving select *.

Try to use all the primary key fields in the where clause.

Thanks,

Srinivas