offset in where condition

[tarangini_katta]

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

[tarangini_katta]

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

[faisalatsap]

Hi,

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

Kind Regards,

Faisal

[Former Member]

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

[viquar_iqbal]

Hi

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

[Former Member]

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

[tarangini_katta]

Hello Mohit,

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

please have a look at this.

Thanks

[Former Member]

Hi Taringini,

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

Regards,

Prashant

[Former Member]

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.

[Former Member]

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.

[Former Member]

hi,

try using '+'

l_client= sy-mandt.

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

[Former Member]

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

[former_member585060]

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

[tarangini_katta]

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,

[faisalatsap]

>

> 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

[faisalatsap]

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

[tarangini_katta]

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,

[faisalatsap]

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

[Former Member]

how many such cases are there like 610. 611. 810,811..

[Former Member]

you can make the value a varibale and still use the LIKE

data:
wk_client      like sy-mandt.

wk_client = '71%'.

select * from whereever where client like wk_client.

[Former Member]

Hi,

Please try the code below.,

data w_client type string.

concatenate i_client+0(2) '%' into w_client.

select * from ztable where client like w_client.

hope this helps you.

regards,

Siddarth

[Former Member]

Hi ,

why dont you try selecting without the where condition.

Later use delete from internal table.

Hope this helps!

Regards,

Prashant

[Former Member]

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

[tarangini_katta]

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.

[Former Member]

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