Using offsets in a select

[Former Member]

Do you know if i can use offests in a select? It doens't want to pull back the infromations. Here is my select.

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).

[Former Member]

Hi,

You can you the offset in the select statment,

<b>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).</b>

The code is ok, but make sure that the field is not having anyu conversion routine and gives the correct value when you use the offset

Regards

Sudheer

[Former Member]

From what I have been gathering I can' use it with a for all entry

[Former Member]

Hi

Yes you can! But I'm not sure it can do it if it uses FOR ALL ENTRIES because this option doesn't use the header line of the internal table.

Max

[Former Member]

Yes it gives me the following warning when I do a syntax check which i don't know what it means or how to fix.

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

"FISCPER" in dieser Bedingung ignoriert.

[Former Member]

HI,

that means the field FISCPER is unable to access the condition and it is ignoring.

my suggestion is use loop and endloop..

<b>Loop at T_ICORDCSTA1.

SELECT BELNR KOKRS BUZEI GJAHR EBELN EBELP

INTO TABLE T_COEP

FROM COEP

WHERE BELNR = T_ICORDCSTA1-BELNR

AND KOKRS = T_ICORDCSTA1-KOKRS

AND BUZEI = T_ICORDCSTA1-BUZEI

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

Endloop.</b>

REgards

SAB

[Former Member]

IT SAYS IT HAS NO HEADER LINE DEFINED.

[Former Member]

I think it should be:

APPENDING TABLE T_COEP

Rob

[Former Member]

You'll have to loop at the table into a work area and use the work area for the SELECT.

Rob

[Former Member]

So just create a work area of type table and then use my offset there? i am not sure i follow

[Former Member]

No - not of type table. You need to loop at the table into something the SELECT statement can use.

Rob

[Former Member]

Like:

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.

Rob

[Former Member]

HI Mick,

follow the way as Rob given... i was not sure that u r using Occurs for ur internal table or not.. u need to provide work area to append in t_coep.

or if u use itab like........

<b>Data T_ICORDCSTA1 like ICORDCSTA1 occurs 0 with header line.

Loop at T_ICORDCSTA1.

SELECT BELNR KOKRS BUZEI GJAHR EBELN EBELP

INTO TABLE T_COEP

FROM COEP

WHERE BELNR = T_ICORDCSTA1-BELNR

AND KOKRS = T_ICORDCSTA1-KOKRS

AND BUZEI = T_ICORDCSTA1-BUZEI

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

Endloop.</b>

<b>

Reward to all Helpful answers........</b>

<b>

Reward to all Helpful answers........</b>

Regards

SAB

[Former Member]

I got most to work but now there is someting wrong with the following statement where i do a read into w_coep

IF NOT T_COEP IS INITIAL.

READ TABLE T_COEP INTO W_COEP

WITH KEY BELNR = W_ICORDCSTA1-BELNR

KOKRS = W_ICORDCSTA1-KOKRS

BUZEI = W_ICORDCSTA1-BUZEI

GJAHR = W_ICORDCSTA1-FISCPER+0(4)

BINARY SEARCH.

• FILL FIELDS WITH DATA FROM COEP (CO OBJECT LINE ITEMS TABLE)

IF SY-SUBRC = 0.

W_ICORDCSTA1-ZZ_PURCHORD1 = W_COEP-EBELN.

W_ICORDCSTA1-ZZ_PURORDLI1 = W_COEP-EBELP.

MODIFY T_ICORDCSTA1 FROM W_ICORDCSTA1 INDEX L_TABIX.

CLEAR W_ICORDCSTA1.

ENDIF.

ENDIF.

CLEAR W_COEP.

CLEAR W_ICORDCSTA1.

ENDLOOP.

ENDIF.

REFRESH C_T_DATA.

C_T_DATA[] = T_ICORDCSTA1[].

REFRESH T_COEP.

FREE T_COEP.

FREE T_ICORDCSTA1.

[Former Member]

Hello,

Are U sorting the fields of the table T_COEP liike

<b>SORT T_CEOP BY KOKRS BUYEI GJAHR</b>

Check this.

Vasanth

[Former Member]

This is a different question. Why don't you mark this thread as answered and open a new thread for the new question?

Rob

[Former Member]

Okay thanks. I just did that. I noticed that even though my talbes are the same the fiedls being moved in are not correct. Like it is shiftled over

[Former Member]

Hi Mick,

The query should still work. U can use offset like that. I think you are not getting year offset in T_ICORDCSTA1-FISCPER correctly.

Just debug the program before executing select query to find the value T_ICORDCSTA1-FISCPER in T_ICORDCSTA1 and see how the value is stored in the field and make changes accordingly in your query.

Cheers,

Vikram

Mark for helpful replies!!

[Former Member]

It gives me the error above so it will not work