topic need Help in query. in Application Development and Automation Discussions

need Help in query.

Former Member — Wed, 19 Aug 2009 09:14:46 GMT

Dear Gurus

is this Query Rite.?

SELECTION-SCREEN BEGIN OF SCREEN 100 AS SUBSCREEN.

SELECT-OPTIONS: s_matnr FOR mseg-matnr," OBLIGATORY, " Material
                s_bwkey FOR mseg-werks OBLIGATORY, "Plant
                s_date FOR mkpf-budat OBLIGATORY .   " Date
SELECTION-SCREEN END OF SCREEN 100.

SELECTION-SCREEN: BEGIN OF TABBED BLOCK b2 FOR 4 LINES,
                  TAB (50) title USER-COMMAND '' DEFAULT SCREEN 100,
                  END OF BLOCK b2.

SELECT *
    FROM  mbewh
    INTO TABLE mb
    WHERE matnr IN s_matnr
    AND  lfgja EQ year
    AND    bwkey IN s_bwkey.

SELECT *
    INTO CORRESPONDING FIELDS OF TABLE bi
    FROM bsim INNER JOIN mseg
    ON bsim~mandt = mseg~mandt
    AND bsim~gjahr = mseg~mjahr
    AND bsim~bwtar = mseg~bwtar
    FOR ALL entries IN s_matnr
    WHERE mseg~matnr = s_matnr-low
    AND bwkey IN s_bwkey
    AND budat IN s_date.

  SORT bi BY matnr bwkey.

LOOP AT mb WHERE lfmon EQ date AND bwkey IN s_bwkey.

    itab-bwkey  = mb-bwkey.
    itab-matnr  = mb-matnr.

    SELECT SINGLE meins FROM bsim INTO itab-meins
      WHERE matnr IN s_matnr
      AND  budat IN s_date.

    SELECT SINGLE maktx FROM makt INTO itab-maktx
      WHERE matnr EQ itab-matnr.

    SELECT SINGLE lbkum FROM mbewh INTO itab-opqty
      WHERE matnr EQ itab-matnr
      AND   lfmon EQ pdate
      AND   lfgja EQ year.

    SELECT SINGLE salk3 FROM mbewh INTO itab-opbal
      WHERE matnr EQ itab-matnr
      AND lfmon EQ pdate
      AND lfgja EQ year.

    LOOP AT bi WHERE matnr EQ itab-matnr AND bwkey EQ mb-bwkey.
      IF bi-shkzg EQ 'S'.
        itab-recp = itab-recp + bi-menge.
        itab-recpval = itab-recpval + bi-dmbtr.
      ELSEIF bi-shkzg EQ 'H'.
        itab-issue = itab-issue + bi-menge.
        itab-issval = itab-issval + bi-dmbtr.
      ENDIF.
    ENDLOOP.

    itab-cloqty = mb-lbkum.
    itab-clobal = mb-salk3.
    itab-rpu    = mb-verpr.

    APPEND itab.
    CLEAR itab-recp.
    CLEAR itab-recpval.
    CLEAR itab-issval.
    CLEAR itab-issue.
    CLEAR itab-opqty.
    CLEAR itab-opbal.
    CLEAR itab-cloqty.
    CLEAR itab-clobal.

  ENDLOOP.

Re: need Help in query.

jaideepsharma — Mon, 24 Aug 2009 07:44:56 GMT

Hi,

There are many queries in your code. Which one are you curious to know about ?

KR Jaideep,

Re: need Help in query.

Former Member — Mon, 24 Aug 2009 07:45:58 GMT

All!..

Mostly the inner join one!

Re: need Help in query.

Former Member — Mon, 24 Aug 2009 07:54:44 GMT

Re: need Help in query.

Former Member — Mon, 24 Aug 2009 08:03:37 GMT

Hello Saad,


SELECT *
    INTO CORRESPONDING FIELDS OF TABLE bi
    FROM bsim INNER JOIN mseg
    ON bsim~mandt = mseg~mandt
    AND bsim~gjahr = mseg~mjahr
    AND bsim~bwtar = mseg~bwtar
    FOR ALL entries IN s_matnr
    WHERE mseg~matnr = s_matnr-low
    AND bwkey IN s_bwkey
    AND budat IN s_date.

You query looks fine only thing is it is not advised to use * while selecting fields from database.

You should follow abap practice to avoid any performace issues.

Thanks,

Augustin.

Re: need Help in query.

jaideepsharma — Mon, 24 Aug 2009 08:09:35 GMT

Hi,

Your inner join seems to be fine but can you look for a possibility to write a logic where you can move

SELECT SINGLE meins FROM bsim INTO itab-meins
      WHERE matnr IN s_matnr
      AND  budat IN s_date.
 
    SELECT SINGLE maktx FROM makt INTO itab-maktx
      WHERE matnr EQ itab-matnr.
 
    SELECT SINGLE lbkum FROM mbewh INTO itab-opqty
      WHERE matnr EQ itab-matnr
      AND   lfmon EQ pdate
      AND   lfgja EQ year.
 
    SELECT SINGLE salk3 FROM mbewh INTO itab-opbal
      WHERE matnr EQ itab-matnr
      AND lfmon EQ pdate
      AND lfgja EQ year.

out of loop. It may increase the performance of the program.

KR Jaideep,