topic Re: Using for all entries in Application Development and Automation Discussions

Using for all entries

Former Member — Tue, 13 Feb 2007 20:33:17 GMT

Is this okay to use?

I have the following code and could do it either way but I don't know what is the best

SELECT BELNR KOKRS BUZEI GJAHR EBELN EBELP

INTO TABLE T_COEP

FROM COEP

FOR ALL ENTRIES IN T_ICORDCSTA1

WHERE BELNR = T_ICORDCSTA1-BELNR

AND KOKRS = T_ICORDCSTA1-KOKRS

AND BUZEI = T_ICORDCSTA1-BUZEI

AND GJAHR = T_ICORDCSTA1-FISCPER+0(4).

DATA wa_icordcsta1 type icordcsta1.

LOOP AT t_icordcsta1 INTO wa_icordcsta1.

SELECT belnr kokrs buzei gjahr ebeln ebelp

APPENDING TABLE t_coep

FROM coep

WHERE belnr = wa_icordcsta1-belnr

AND kokrs = wa_icordcsta1-kokrs

AND buzei = wa_icordcsta1-buzei

AND gjahr = wa_icordcsta1-fiscper+0(4).

ENDLOOP.

Re: Using for all entries

RichHeilman — Tue, 13 Feb 2007 20:39:22 GMT

For ALL ENTRIES would be one trip to the DB, which is better than the LOOP and SELECT.

You may want to award points to your other thread for any helpful answers and mark as closed.

REgards,

Rich Heilman

Re: Using for all entries

Former Member — Tue, 13 Feb 2007 20:43:12 GMT

Well I thought that would be the case but i keep getting this message. I did a translation on it and it says When using FOR ALL ENTRIES becomes the length specification for "FISCPER" in this condition ignores. So it doens't like this statement but I don't really know why

AND GJAHR = T_ICORDCSTA1-FISCPER+0(4)

Bei der Verwendung von FOR ALL ENTRIES wird die Längenangabe für

"FISCPER" in dieser Bedingung ignoriert.

Re: Using for all entries

Former Member — Tue, 13 Feb 2007 21:04:27 GMT

I think we went through this on your other thread. You can't use the offset when using FOR ALL ENTRIES. So you're left with the loop.

An alternative would be to loop through t_icordcsta1 and create an additional field (say GJAHR) with just the first four characters of FISCPER.

Rob

Re: Using for all entries

christian_wohlfahrt — Tue, 13 Feb 2007 21:15:21 GMT

Hi Mick!

The translation is correct; the message is in general correct and should be followed - still there is one very small exception: all characters of T_ICORDCSTA1-FISCPER+4(n) will be ignored, too - only the same length of GJAHR will be taken for the compare.

So a select with AND GJAHR = T_ICORDCSTA1-FISCPER should work. Only selects with AND GJAHR = T_ICORDCSTA1-FISCPER+n(m) with n > 0 will not work with FOR ALL ENTRIES.

Regards,

Christian

Re: Using for all entries

Former Member — Tue, 13 Feb 2007 21:28:36 GMT

Hi,

Declare one more field in the internal table T_ICORDCSTA1 which will hold the value T_ICORDCSTA1-FISCPER0(4) for every entry and then during FOR ALL ENTRIES, use that field instead of using offset length i.e T_ICORDCSTA1-FISCPER0(4).


TYPES : BEGIN OF X_ICORDCSTA1,
                .
                .
                .
              year(4)  TYPE  N,
              END    OF X_ICORDCSTA1.

DATA : T_ICORDCSTA1 TYPE TABLE OF X_ICORDCSTA1.
.
.
.
LOOP AT T_ICORDCSTA1 INTO X_ICORDCSTA1.
X_ICORDCSTA1-year = T_ICORDCSTA1-FISCPER+0(4).
MODIFY TABLE T_ICORDCSTA1 FROM X_ICORDCSTA1 TRANSPORTING year.
ENDLOOP.

SELECT BELNR KOKRS BUZEI GJAHR EBELN EBELP
INTO TABLE T_COEP
FROM COEP
FOR ALL ENTRIES IN T_ICORDCSTA1
WHERE BELNR = T_ICORDCSTA1-BELNR
AND KOKRS = T_ICORDCSTA1-KOKRS
AND BUZEI = T_ICORDCSTA1-BUZEI
AND GJAHR = T_ICORDCSTA1-year.

IF sy-subrc <> 0.

ENDIF.

Reward points if the answer is helpful.

Regards,

Mukul

Message was edited by:

Mukul R. Kulkarni

Re: Using for all entries

Former Member — Tue, 13 Feb 2007 21:44:53 GMT

Well it lets me and returns the right entries. so this seems to be better for performance than just doing 1 at a time right?

Re: Using for all entries

Former Member — Tue, 13 Feb 2007 21:55:03 GMT

I see it's a warning and not a hard error. Still, I'd get rid of it. Warnings sometimes become errors in higher releases.

The next programmer to have to change the program won't appreciate getting a warning every time he or she has to do a syntax check either.

Rob

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 10:40:49 GMT

Hello,

Go for FOR ALL ENTRIES. It will have good performance.

In the second case a database access will happen for each and every row.

Regs,

Venkat Ramanan N

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 13:56:38 GMT

well problem with tath approach is then it hits the DB everytime versus just once.

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 13:56:59 GMT

yes I do agree.

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 14:48:19 GMT

Not if you create the extra field.

Rob

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 14:50:54 GMT

I guesss i don't follow becuase I would have to do a loop which is 1 at a time. Do you have a smaple of what you mean?

Re: Using for all entries

Former Member — Wed, 14 Feb 2007 15:15:48 GMT

Yes - try:


REPORT ztest MESSAGE-ID 00.

DATA: BEGIN OF t_coep OCCURS 0.
        INCLUDE STRUCTURE coep.
DATA: END   OF t_coep.

DATA: BEGIN OF t_icordcsta1 OCCURS 0.
        INCLUDE STRUCTURE icordcsta1.
DATA: END   OF t_icordcsta1.

DATA: BEGIN OF itab OCCURS 0.
        INCLUDE STRUCTURE icordcsta1.
DATA:   gjahr LIKE coep-gjahr,
      END   OF itab.

LOOP AT t_icordcsta1 INTO itab.
  itab-gjahr = t_icordcsta1-fiscper(4).
  APPEND itab.
ENDLOOP.

SORT itab BY kokrs belnr buzei gjahr.
DELETE ADJACENT DUPLICATES FROM itab COMPARING kokrs belnr buzei gjahr.

SELECT belnr kokrs buzei gjahr ebeln ebelp
  INTO TABLE t_coep
  FROM coep
  FOR ALL ENTRIES IN itab
  WHERE kokrs = itab-kokrs
  AND   belnr = itab-belnr
  AND   buzei = itab-buzei
  AND   gjahr = itab-gjahr.

Rob

Re: Using for all entries

Former Member — Sat, 17 Feb 2007 03:59:00 GMT

Mick,

I had the same problem used COVP which is a innner join between COBK and COEP on KOKRS & BELNR and it gave me very good results.

Ex :


*--Get the Complete COEP Details
  CLEAR : it_covp,
                it_covp[].

  SELECT *
  FROM covp
  INTO TABLE it_covp
  WHERE    lednr      EQ  '00'
       AND    objnr      EQ  wa_caufv_s-objnr. "Order Number in OBJNR format