2 questions...

[aris_hidalgo]

Hi guys!

Question 1.

I have pasted below the select statement I have made. I am not sure if this is the most optimal query. Please post you alternative SQL that is faster and efficient.

SELECT

MATNR

MATKL

MEINS

FROM MARA

INTO TABLE ITAB_6

WHERE MATNR IN SO_MATNO AND

MATKL IN SO_MGRP.

SELECT

WERKS

MATNR

LABST

LGORT

LGPBE

FROM MARD

INTO TABLE ITAB_5

FOR ALL ENTRIES IN ITAB_6

WHERE MATNR EQ ITAB_6-MATNR AND

WERKS EQ SP_PLANT AND

LGORT IN SO_SLOC AND

LGPBE IN SO_SBIN AND

LABST <> 0.

LOOP AT ITAB_6.

LOOP AT ITAB_5 WHERE MATNR = ITAB_6-MATNR.

MOVE: ITAB_5-WERKS TO ITAB_1-GD_RPLANT,

ITAB_5-MATNR TO ITAB_1-GD_RMATNO,

ITAB_5-LABST TO ITAB_1-GD_RQTY,

ITAB_5-LGORT TO ITAB_1-GD_RSLOC,

ITAB_5-LGPBE TO ITAB_1-GD_RSBIN,

ITAB_6-MATKL TO ITAB_1-GD_RMGRP,

ITAB_6-MEINS TO ITAB_1-GD_RUNIT.

APPEND ITAB_1.

ENDLOOP.

ENDLOOP.

Question 2.

Please compare the 2 QSL query and please tell me if it is the same(e.g. gets the same data, etc.)

-

---

select 1.----

---

SELECT A~WERKS

A~MATNR

A~SLABS

A~LGORT

A~LIFNR

B~MATKL

B~MEINS

INTO (ITAB_3-GD_CPLANT, ITAB_3-GD_CMATNO, ITAB_3-GD_CQTY,

ITAB_3-GD_CSLOC, ITAB_3-GD_CVENDOR, ITAB_3-GD_CMGRP,

ITAB_3-GD_CUNIT)

FROM ( MKOL AS A INNER JOIN MARA AS B ON A_{MATNR = B}MATNR )

WHERE A~WERKS = SP_PLANT AND

A~MATNR IN SO_MATNO AND

A~LGORT IN SO_SLOC AND

A~LIFNR IN SO_VCODE AND

A~SLABS <> 0 AND

A~SOBKZ = 'K' AND

B~MATKL IN SO_MGRP.

APPEND ITAB_3.

ENDSELECT.

-

---

select 2---

SELECT

WERKS

MATNR

SLABS

LGORT

LIFNR

FROM MKOL

INTO TABLE ITAB_7

WHERE WERKS = SP_PLANT AND

MATNR IN SO_MATNO AND

LGORT IN SO_SLOC AND

LIFNR IN SO_VCODE AND

SLABS <> 0 AND

SOBKZ = 'K'.

SELECT

MATNR

MATKL

MEINS

FROM MARA

INTO TABLE ITAB_6

FOR ALL ENTRIES IN ITAB_7

WHERE MATNR EQ ITAB_7-MATNR AND

MATKL IN SO_MGRP.

LOOP AT ITAB_7.

READ TABLE ITAB_6 WITH KEY MATNR = ITAB_7-MATNR.

MOVE: ITAB_7-WERKS TO ITAB_3-GD_CPLANT,

ITAB_7-MATNR TO ITAB_3-GD_CMATNO,

ITAB_7-SLABS TO ITAB_3-GD_CQTY,

ITAB_7-LGORT TO ITAB_3-GD_CSLOC,

ITAB_7-LIFNR TO ITAB_3-GD_CVENDOR,

ITAB_6-MATKL TO ITAB_3-GD_CMGRP,

ITAB_6-MEINS TO ITAB_3-GD_CUNIT.

APPEND ITAB_3.

ENDLOOP.

Thanks guys!

[Former Member]

Hi

In SE30 runtime analysis, in that go to utilities menu

you can find the option TIPS&TRICKS there paste the selects and see which is optimized code..

regards

vijay

[Former Member]

the optimal query is

SELECT A~WERKS

A~MATNR

A~SLABS

A~LGORT

A~LIFNR

B~MATKL

B~MEINS

INTO (ITAB_3-GD_CPLANT, ITAB_3-GD_CMATNO, ITAB_3-GD_CQTY,

ITAB_3-GD_CSLOC, ITAB_3-GD_CVENDOR, ITAB_3-GD_CMGRP,

ITAB_3-GD_CUNIT)

FROM ( MKOL AS A INNER JOIN MARA AS B ON A_{MATNR = B}MATNR )

WHERE A~WERKS = SP_PLANT AND

A~MATNR IN SO_MATNO AND

A~LGORT IN SO_SLOC AND

A~LIFNR IN SO_VCODE AND

A~SLABS <> 0 AND

A~SOBKZ = 'K' AND

B~MATKL IN SO_MGRP.

APPEND ITAB_3.

ENDSELECT.

This will fetch data much faster and will be the same data as of that from which 2 select statements & then loop...endloop does.

Hope understood, Pl. award appropriate points.

[Former Member]

hi

for Q1:

SELECT MATNR MATKL MEINS FROM MARA INTO CORRESPONDING FIELDS OF TABLE ITAB_6 WHERE MATNR IN SO_MATNO AND MATKL IN SO_MGRP.

IF SY-SUBRC = 0.

IF ITAB_6[] NOT INITIAL.

SELECT MATNR MATKL MEINS WERKS LABST LGORT LGPBE FROM MARD

INTO CORRESSPONDING FIELDS OF ITAB_5

FOR ALL ENTRIES IN ITAB_6

WHERE MATNR = ITAB_6-MATNR

AND WERKS EQ SP_PLANT AND

LGORT IN SO_SLOC AND

LGPBE IN SO_SBIN AND

LABST <> 0.

ENDIF.

ENDIF.

BY USING THIS LOGIC U CAN DECRESE LOAD ON DB. THIS IS A FATSER WAY.

ALWAYS USE FOR ALL ENTRIES

REGARDS

VIJAY

[former_member186741]

I think the jury is still out on what is the optimal way to code sql. Make the comparison yourself in your own environment for your own purposes rather than relying on advice from people in other sites which may well have very different environments to your own. Use SE30 to run similar programs with the the alternative sql's and this should help you make a decision. Of course, another problem is that often the production environment is different from the one you test in! Good luck.