topic Re: string in where clause in Application Development and Automation Discussions

string in where clause

Former Member — Wed, 07 Dec 2005 15:06:10 GMT

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

Re: string in where clause

Former Member — Wed, 07 Dec 2005 15:20:59 GMT

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.

Re: string in where clause

Former Member — Wed, 07 Dec 2005 15:22:03 GMT

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.

Re: string in where clause

FredericGirod — Wed, 07 Dec 2005 15:25:45 GMT

Hi Stefan,

I think you have to look the type table: hashed

good luck

Frédéric

Re: string in where clause

Former Member — Wed, 07 Dec 2005 15:41:11 GMT

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

Re: string in where clause

Former Member — Wed, 07 Dec 2005 15:49:25 GMT

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

Re: string in where clause

Former Member — Thu, 08 Dec 2005 08:33:19 GMT

@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