topic Re: Using offsets in a select in Application Development and Automation Discussions

Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:02:32 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:05:12 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:06:40 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:11:06 GMT

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.

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:33:14 GMT

HI,

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

my suggestion is use loop and endloop..

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.

REgards

SAB

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:42:23 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:51:11 GMT

IT SAYS IT HAS NO HEADER LINE DEFINED.

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:51:34 GMT

I think it should be:


APPENDING TABLE T_COEP

Rob

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:52:55 GMT

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!!

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 16:53:10 GMT

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

Rob

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 17:01:37 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 17:02:30 GMT

It gives me the error above so it will not work

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 17:06:56 GMT

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

Rob

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 17:12:29 GMT

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

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 17:54:01 GMT

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

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.

Reward to all Helpful answers........

Reward to all Helpful answers........

Regards

SAB

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 19:28:00 GMT

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.

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.

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 19:30:08 GMT

Hello,

Are U sorting the fields of the table T_COEP liike

SORT T_CEOP BY KOKRS BUYEI GJAHR

Check this.

Vasanth

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 19:31:41 GMT

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

Rob

Re: Using offsets in a select

Former Member — Tue, 13 Feb 2007 19:46:00 GMT

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