Solved: Please help - NE where condition in SELECT stateme...

former_member367551 · ‎2009 Jan 02

Dear experts,

I am posting a section of my codes here for your review on performance tuning.

In my second select statement, I used a "NE" where condition. I read somewhere in this forum that using "NE" where condition is not a good decision for improving codes' performance. What alternatives can I have to achieve the same purpose? May I use "NOT IN" here instead? Or do I use a LOOP for this (a rather manual way)?

Just to let you all know that I still consider myself quite inexperienced in ABAP - please also let me know how I can better improvise my programming techniques in the posted codes here too.

I will be most glad to provide you with further information if needed - just let me know.

Many THANKS in advance!

IF p_noncis = 'X'.      " Non CIS category of spend selected

    " zfi_cis_mat_grp is a bespoke table that stores all CIS MATKL 
    " and it has two fields only - MANDT and MATKL
    SELECT * FROM zfi_cis_mat_grp     
    INTO TABLE gt_cis_mat_grp.

    IF gt_cis_mat_grp IS NOT INITIAL.

      SELECT ebeln
             ebelp
	      matkl
      FROM ekpo
      INTO TABLE gt_ekpo
      FOR ALL ENTRIES IN gt_cis_mat_grp
      WHERE matkl NE gt_cis_mat_grp-matkl.    " NE where condition - is this OK?

    ENDIF.

    IF gt_ekpo IS NOT INITIAL.

      IF s_sakto IS NOT INITIAL.

        SELECT ebeln
               ebelp
               sakto
        FROM ekkn
        INTO TABLE gt_ekkn
        FOR ALL ENTRIES IN gt_ekpo
        WHERE ebeln = gt_ekpo-ebeln AND
              ebelp = gt_ekpo-ebelp AND
              sakto IN s_sakto.

        IF gt_ekkn IS NOT INITIAL.

          SELECT bukrs
                 lifnr
                 belnr
                 budat
                 cpudt
                 xblnr
                 ebeln
                 ebelp
                 zfbdt
                 zterm
                 zlspr
          FROM bsik
          INTO TABLE gt_bsik
          FOR ALL ENTRIES IN gt_ekkn
          WHERE bukrs IN s_bukrs AND
                lifnr IN s_lifnr AND
                budat IN s_budat AND
                cpudt IN s_cpudt AND
                xblnr IN s_xblnr AND
                ebeln = gt_ekkn-ebeln AND
                ebelp = gt_ekkn-ebelp AND
                qsskz NE ''.

        ENDIF.

      ELSE.

        SELECT bukrs
               lifnr
               belnr
               budat
               cpudt
               xblnr
               ebeln
               ebelp
               zfbdt
               zterm
               zlspr
        FROM bsik
        INTO TABLE gt_bsik
        FOR ALL ENTRIES IN gt_ekpo
        WHERE bukrs IN s_bukrs AND
              lifnr IN s_lifnr AND
              budat IN s_budat AND
              cpudt IN s_cpudt AND
              xblnr IN s_xblnr AND
              ebeln = gt_ekpo-ebeln AND
              ebelp = gt_ekpo-ebelp AND
              qsskz NE ''.

      ENDIF.

    ENDIF.

  ELSE.      " Complete list of category of spend selected

    SELECT bukrs
           lifnr
           belnr
           budat
     	    cpudt
           xblnr
           ebeln
           ebelp
           zfbdt
           zterm
           zlspr
    FROM bsik
    INTO TABLE gt_bsik
    WHERE bukrs IN s_bukrs AND
          lifnr IN s_lifnr AND
          budat IN s_budat AND
          cpudt IN s_cpudt AND
          xblnr IN s_xblnr AND
          qsskz NE ''.

  ENDIF.

Former Member · ‎2009 Jan 02

I guess you read the so-call performance recommendation 'USE FOR ALL ENTRIES instead of JOINS'

here I would try a subquery or a join:

subquery:


 
      SELECT ebeln  ebelp matkl
                   FROM ekpo
                   INTO TABLE gt_ekpo
                   WHERE matkl NE (    SELECT matkl 
                                                    FROM zfi_cis_mat_grp. )

Siegfried

Former Member · ‎2009 Jan 02

Hi,

If you want to remove th NE option then try this way..

SELECT bukrs
           lifnr
           belnr
           budat
     	    cpudt
           xblnr
           ebeln
           ebelp
           zfbdt
           zterm
           zlspr
    FROM bsik
    INTO TABLE gt_bsik
    WHERE bukrs IN s_bukrs AND
          lifnr IN s_lifnr AND
          budat IN s_budat AND
          cpudt IN s_cpudt AND
          xblnr IN s_xblnr .
IF SY-SUBRC EQ 0.
  Delete gt_bsik where qsskz EQ ' '.
ENDIF.

former_member367551 · ‎2009 Jan 02

Thanks a lot, Avinash, I will certainly try this out and take note of it..

But what about this, regarding the "NE" where condition:-

SELECT ebeln
       ebelp
       matkl
FROM ekpo
INTO TABLE gt_ekpo
FOR ALL ENTRIES IN gt_cis_mat_grp
WHERE matkl NE gt_cis_mat_grp-matkl.    " NE where condition - is this OK?

Former Member · ‎2009 Jan 02

Hi,

One way of avoiding NE is , get all the records in T023(Check table for MAKTL). Delete your Ztable entries from T023 and use it in Select for EKPO. But this will depend on the no of entries you have in T023 and the Ztable where you are storing the Maktl.

Hope this helps

Raj

Former Member · ‎2009 Jan 02

HI,

For that it's Ok ..as you don't have any options to remove that.

ThomasZloch · ‎2009 Jan 02

>


>       FOR ALL ENTRIES IN gt_cis_mat_grp
>       WHERE matkl NE gt_cis_mat_grp-matkl.    " NE where condition - is this OK?

As soon as you have more than one entry in gt_cis_mat_grp, then your select will return all EKPO rows, because the "for all entries" will be translated into something like WHERE MATKL NE '1111' OR MATKL NE '2222'.

Better fill a range with those MATKL values from zfi_cis_mat_grp, then select all materials from MARA that are NOT IN that range and finally select all EKPO rows for the remaining materials (try a join of EKPO and MARA).

Thomas

Former Member · ‎2009 Jan 02

I guess you read the so-call performance recommendation 'USE FOR ALL ENTRIES instead of JOINS'

here I would try a subquery or a join:

subquery:


 
      SELECT ebeln  ebelp matkl
                   FROM ekpo
                   INTO TABLE gt_ekpo
                   WHERE matkl NE (    SELECT matkl 
                                                    FROM zfi_cis_mat_grp. )

Siegfried

former_member367551 · ‎2009 Jan 06

Thanks so much for all the help offered here.

I really do appreciate it.

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

Please help - NE where condition in SELECT statement