topic Re: offset in where condition in Application Development and Automation Discussions

offset in where condition

tarangini_katta — Wed, 11 Feb 2009 12:54:43 GMT

Hi All,

How to use offset in where condition like

l_client= sy-mandt.

Select * from ztable where client(2) = l_client(2).

but this hsowing error message.

Please how to use offset in where condition.

Thanks

Re: offset in where condition

faisalatsap — Wed, 11 Feb 2009 12:57:28 GMT

Hi,

I think you can't use it in the Select Statement.

Kind Regards,

Faisal

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 12:59:02 GMT

Hi Tarangini

Offset specification in WHERE clause of the SELECT query throws a syntax error.

You could instead


SELECT * FROM ztable.
DELETE FROM ztable where client(2) NE l_client(2).

Pushpraj

Re: offset in where condition

viquar_iqbal — Wed, 11 Feb 2009 12:59:26 GMT

you need to write like this

l_client= sy-mandt.

w_client = l_client(2)

Select * from ztable where client = w_client

we cannot use offset for client as it reads the contents completely and not in parts

Thanks

Viquar Iqbal

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:00:02 GMT

hi...

go with following code THIS IS A WORKING CODE

you cannot use assignment in where clause along with offset

data: w_cahr(2).

l_client= sy-mandt.

w_cahr = l_client(2).

Select * from ztable where client(2) = w_cahr.

regards

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:01:45 GMT

HI,

Select single mandt into l_client from ztable

CLIENT SPECIFIED

where mandt = l_client+0(2).

Hope this is the condition what you want to write.

Regards,

Phani.

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:03:29 GMT

what u can do is fetch the value of the value with the offset into a variable.

in the select query, do comparision wth this variable.

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:03:52 GMT

hi,

try using '+'

l_client= sy-mandt.

Select * from ztable where client(2) = l_client+0(2).

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:04:12 GMT

hi,

we can use offsets in SELECT statement when we pass the data base values directly to the internal table fields.

sample code:

select ......

....... from (db table) onto (it_tab-field1, ........).

Re: offset in where condition

tarangini_katta — Wed, 11 Feb 2009 13:04:44 GMT

Hello Mohit,

it is throwing error message client(2) does not exits.

please have a look at this.

Thanks

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:06:48 GMT

Hi Taringini,

You can use offset after the = sign in where clause not before that.

Regards,

Prashant

Re: offset in where condition

former_member585060 — Wed, 11 Feb 2009 13:14:30 GMT

Hi,

You can't use offsets in SELECT statements, instead fetch the whole data from the table, and then use IF and ENDIF conditions using offsets and delete the data from the table which does not meet your requirment.

Declare an internal table of structure ztable with addition of flag field.

SELECT * FROM ztable INTO TABLE it_itab .

LOOP AT it_itab INTO wa_itab.
w_client = wa_itab-client+0(2).
IF w_client = l_client.
    wa_itab-flag = 'X'.
    MODIFY it_itab INDEX sy-tabix FROM wa_itab TRANSPORTING flag.
ENDIF.

DELETE it_itab WHERE flag NE 'X'.

Regards

Bala Krishna.

Edited by: Bala Krishna on Feb 11, 2009 6:45 PM

Re: offset in where condition

tarangini_katta — Wed, 11 Feb 2009 13:15:20 GMT

HI all,

I have z table like

with thae values

123 710

456 720

123 710

I want to get the records who is having offset 71 to my internal table.

Can you please anybody help me.

Thanks,

Re: offset in where condition

faisalatsap — Wed, 11 Feb 2009 13:19:58 GMT


> HI all,
> 
> I have z table like 
> 
> with thae values 
> 
> 123 710
> 456 720
> 123 710
> 
> I want to get the records who is having offset 71 to my internal table.
> 
> Can you please anybody help me.
> 
> Thanks,

Hi,

Test the following Sample Code it will work for you.

DATA: it_kna1 LIKE STANDARD TABLE OF kna1 WITH HEADER LINE.
DATA: l_client LIKE sy-mandt.
l_client = sy-mandt.

SELECT * from kna1 CLIENT SPECIFIED
  into CORRESPONDING FIELDS OF TABLE it_kna1
  WHERE mandt like '71%'.

Kind Regards,

Faisal

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:21:34 GMT

loop at itab into wa_itab.

if wa_itab-value+0(2) = '71'. " value that u need to chk for offset

append wa_itab1-value to wa_itab-value.

append wa_itab1 to it_itab1. " itab1 will have value that hve 71 as offset.

else.

continue.

endif.

endloop.

itab1 should be of same structure as itab.

Edited by: Kalyan Chakravarthi on Feb 11, 2009 2:22 PM

Re: offset in where condition

tarangini_katta — Wed, 11 Feb 2009 13:34:53 GMT

Hi all,

Thanks for all of u r efforts.

I will tell my problem clearly.

I have z table it is client independent table.

It has recors value client

123 610

345 810

567 820

If i am geeting the data from client 610

I am writing select * from ztable into it_tab where client = l_client.

If i am logging in 611 and getting data any way sy-subrc will be 4.But i need the value of 610 i.e '123' if i am working on client '611' also.

This is my exact problem.

Please help me.

Re: offset in where condition

faisalatsap — Wed, 11 Feb 2009 13:35:52 GMT

Hi,

Is above my Sample code not working for you i think you don't need to select first all in an internal table you can do it in the select as i did it is working fine for me.

My Sample Code Must work for you in your Case.

Please Replay if any Issue.

Select * from ztable where client LIKE '71%'.

Kind Regards,

Faisal

Edited by: Faisal Altaf on Feb 11, 2009 6:36 PM

Re: offset in where condition

Former Member — Wed, 11 Feb 2009 13:37:44 GMT

jus give a variable v_client and gve ite the value 610.

now, in ur select query gce condition as client = v_client.

Edited by: Kalyan Chakravarthi on Feb 11, 2009 2:40 PM

Re: offset in where condition

tarangini_katta — Wed, 11 Feb 2009 13:39:41 GMT

Hi altaf.

I can't hard code the value like '71%'.

beacuse i will have another client '811' then i need to get the value of 810 i.e. '345'.

so i can't hard code and get the value.

Thanks,

Re: offset in where condition

faisalatsap — Wed, 11 Feb 2009 13:43:24 GMT

So you can do like following

IF sy-mandt+0(2) = 71.
  SELECT * FROM kna1 CLIENT SPECIFIED
    INTO CORRESPONDING FIELDS OF TABLE it_kna1
    WHERE mandt LIKE '71%'.
ELSEIF sy-mandt+0(2) = 61.
  SELECT * FROM kna1 CLIENT SPECIFIED
    INTO CORRESPONDING FIELDS OF TABLE it_kna1
    WHERE mandt LIKE '61%'.
ENDIF.

Please Reply if still any Issue,

Kind Regards

Faisal