topic Re: Offset in Select Query in Application Development and Automation Discussions

Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:10:36 GMT

Hi,

Can we use offset in select statement. I was trying to compare the year and month of MBEWH table with the LINV table. MBEWH has seperate fields for year and month but LINV table has a date field.

TYPES:BEGIN OF TY_MBEWH,

lfgja type lfgja,

lfmon type lfmon,

END OF TY_MBEWH.

Data: T_MBEWH TYPE TABLE OF TY_MBEWH,

WA_MBEWH TYPE TY_MBEWH.

Select MBEWH_{LFGJA MBEWH}LFMON

INTO TABLE T_MBEWH FROM MBEWH INNER JOIN LINV

ON MBEWH_{MATNR = LINV}MATNR

where MBEWH_{BWKEY = LINV}WERKS

and MBEWH_{LFGJA >= *LINV}IDATU(4)*

and MBEWH~LFMON > LINV.IDATU+4(2).

When I execute the above code Iam getting "Field LINV~IDATU is unknown. It is neither in one of the specified tables nor defined by a DATA statement.

Best Regards

Suresh

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:12:27 GMT

well you can do that offset actiojn BEFORE yourselect into another variable.

then use this variable in your select.

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:13:54 GMT

Hi,

For this error, declare in the top of the program as TABLES: LINV.We have to define the tables used in the program. Hope this will solve the issue.

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:15:51 GMT

Hi,

Whenever you use offset in select query both the fields length(comparing fields after off setting) should be same.

in your case MBEWH_{LFMON length should equal to the length of LINV}IDATU+4(2). then it works.

Regards,

Sathish Reddy.

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:17:51 GMT

Select MBEWH_{LFGJA MBEWH}LFMON

INTO TABLE T_MBEWH FROM MBEWH INNER JOIN LINV

ON MBEWH_{MATNR = LINV}MATNR

where MBEWH_{BWKEY = LINV}WERKS

and MBEWH_{LFGJA >= LINV}IDATU(4)

and MBEWH~LFMON > LINV.IDATU+4(2).

i think this "LINV.IDATU4(2)" instead should be LINV~IDATU4(2).

Re: Offset in Select Query

matt — Thu, 28 Aug 2008 10:18:01 GMT


> Hi,
> For this error, declare in the top of the program as TABLES: LINV.We have to define the tables used in the program. Hope this will solve the issue.

Not in any recent version of SAP you don't.

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:18:03 GMT

No we cannot use offset in select query.

take this LINV~IDATU(4) in some other variable and use that variable in query.

Amit.

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 10:20:29 GMT

Hi Suresh,

Try it this way:

W_YEAR  = LINV-IDATU+0(4).
W_MONTH = LINV-IDATU+4(2).

Select MBEWH~LFGJA MBEWH~LFMON 
INTO TABLE T_MBEWH FROM MBEWH INNER JOIN LINV
ON MBEWH~MATNR EQ LINV~MATNR
where MBEWH~BWKEY EQ LINV~WERKS
and MBEWH~LFGJA GE W_YEAR
and MBEWH~LFMON GT W_MONTH.

With luck,

Pritam.

Re: Offset in Select Query

former_member217544 — Thu, 28 Aug 2008 10:23:20 GMT

Hi ,

Right hand side of select query should contain some variable or constants. As said in the previous reply first keep the data LINV~IDATU(4) in some variable and use this variable in the select statement.

Regards,

Swarna Munukoti

Edited by: Swarna Munukoti on Aug 28, 2008 12:25 PM

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 11:42:08 GMT

Hi,

I cant take the field LINV~IDATU(4) into a variable as this field is used in inner joins. Iam comparing two table fields using joins.

Also LINV is declared in tables statement.

Best Regards

Suresh

Edited by: mariosuresh on Aug 28, 2008 1:43 PM

Re: Offset in Select Query

kesavadas_thekkillath — Thu, 28 Aug 2008 11:45:55 GMT

LINV.IDATU+4(2)

remove the dot

Re: Offset in Select Query

Former Member — Thu, 28 Aug 2008 11:53:45 GMT

Mario,

First write a select only for LINV table and get the data of field IDATU in some itab-IDATU than use offset of IDATU(4) and take in itab-temp and use this itab-temp varibale in your join ferther.

Amit.