string in where clause

[Former Member]

Hi,

I have a little problem. I want to set a select statement like this:

 select ID string into table tab_string from DBtable
  where ID in range_searchID
    and string like searchstring.

Which is not allowed, because in the DBtable the length of the string is to long for a where clause .

So I found a solution for myself.

First I read all entries of the DBtable into an itab and than I delete the entries I don't want to have.

 delete itab where not ( string cp searchstring and
                         ID in range_searchID ).

But this takes a lot of time and I am using it in a BSP-application, so I have a timelimit before timeout.

If you have an idea of a faster selection, please tell me ;).

mfg

Stefan

[Former Member]

Stefan, you could define itab as a sorted table by ID and perform first only the deletion using the ID restriction clause.

data: itab type sorted table of itab_type with non-unique key ID.

delete itab where not ID in range_searchID.

delete itab where not string cp searchstring.

the first delete sentence will use then a binary search and will hopefully be much faster.

2nd possible solution:

Make the select statement to limit the records only by ID.

select ID string into table tab_string from DBtable

where ID in range_searchID.

• and then delete from itab using the string criteria.

delete itab where not string cp searchstring.

Here you could request an index to be created on the DBtable by id which would make the select statement a lot faster.

[Former Member]

Hi,

Use a range:

ranges: r_string for kna1-name1 .

r_string-low = 'Alexandre' .

r_string-option = 'CP' .

r_string-sign = 'I'.

append r_string.

select * from kna1 where name1 in r_string.

I hope it helps..

Regargds

Alexandre Nogueira.

[FredericGirod]

Hi Stefan,

I think you have to look the type table: hashed

good luck

Frédéric

[Former Member]

Assuming ID is the primary key and is sufficiently selective, you could combine the two methods:

 select ID string into table tab_string from DBtable
  where ID in range_searchID.

Then:

 delete itab where not ( string cp searchstring ).

Rob

[Former Member]

Hi,

Try looping the table by applying the condition, bcoz delete that too on NOT will definitely kill the time.

Ex:

loop at itab where string cp <string>

append into anothere internal table.

endloop.

Regards,

Suman

[Former Member]

@Sergio

the first: isn't really faster if the range is empty, that'S my main problem

the second: I already tried, but if there are more than 4000 to 5000 entries in the range

the select statement just does nothing....

@Alexandre

sorry but my problem is not the searchstring, it's the string inside my DB.

@Frédéric

I will try that, and tell you later if it helps.

@Rob

its the same problem like in Sergios second advice

@Suman

that's what I thought about when I went home.

NOT statements cost a lot of time.

So I just wrote it before I read this, but your answer was very helpful.

/edit: I just took the time and both delete with NOT cp and loop where cp need the same time...

@all

thanks for your help so far

Message was edited by: Stefan Huemer