Solved: Regadrding select statements

Former Member · ‎2008 May 01

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 · ‎2008 May 01

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 · ‎2008 May 01

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 · ‎2008 May 01

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 · ‎2008 May 01

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

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Regadrding select statements