Skip to Content
author's profile photo Former Member
Former Member

string in where clause

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 07, 2005 at 03:20 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 07, 2005 at 03:22 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 07, 2005 at 03:25 PM

    Hi Stefan,

    I think you have to look the type table: hashed

    good luck

    Frédéric

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 07, 2005 at 03:41 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 07, 2005 at 03:49 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 08, 2005 at 08:33 AM

    @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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.