SELECT WHERE a IN range (IGNORING CASE)?

[daniel_humberg]

I do a select on a database table using a range table.

ls_search_criterium-sign   = 'I'.
ls_search_criterium-option = 'CP'
ls_search_criterium-low    = '*abc*'.
"...
SELECT * FROM table INTO TABLE lt_table
         WHERE field IN ltr_search_criteria.

This code finds all entries containing the text "abc".

Now, I would like to also find "ABC" or "Abc".

Is there a way to this without several select statements?

[abdul_hakim]

Hi Daniel,

Try the below code...

ls_search_criterium-sign = 'I'.

ls_search_criterium-option = 'CP'

ls_search_criterium-low = '%abc'.

"...

SELECT * FROM table INTO TABLE lt_table

WHERE field IN ltr_search_criteria.

Abdul

[andreas_mann3]

Yes , you can append your range:

ls_search_criterium-low = 'ABC'.

append ls_search_criterium.

ls_search_criterium-low = 'Abc'.

append ls_search_criterium.

regards Andreas

[daniel_humberg]

Hi Andres,

when I add two lines to my range, they are implicitly connected with AND right? But I need an OR.

Not

SELECT * FROM table WHERE field LIKE "%abc%"
                      AND field LIKE "%ABC%"

but

SELECT * FROM table WHERE field LIKE "%abc%"
                       OR field LIKE "%ABC%"
                       OR field LIKE "%Abc%"
                       OR field LIKE "%aBc%"
                       OR field LIKE "%abC%".

[andreas_mann3]

Hi Daniel,

>when I add two lines to my range, they are implicitly connected with AND right?

NO, but with <b>OR</b>

look ST05:

SELECT                                                                                
*                                                                                
FROM                                                                                
"Z021"                                                                                
WHERE                                                                                
"MANDT" = :A0 AND
 ( "PGTXT" LIKE :A1 OR "PGTXT" LIKE :A2 OR "PGTXT" LIKE :A3 )                                                                                
Variables                                                                                
A0(CH,3)  = 200                                                                         
A1(CH,6)  = %abc%                                                                      
A2(CH,6)  = %Abc%                                                                      
A3(CH,6)  = %ABC%                                                                     

Andreas

[abdul_hakim]

Message was edited by: Abdul Hakim

Message was edited by: Abdul Hakim

[Former Member]

Hi Daniel,

Nice question.

1. Using open sql, it is not possible directly.

2. At present u have only 3 Characters (A,B,C)

If there are more, say A,B,C,D,E,F,G

how many permutation combination can one work out?

3. This technique is not practically feasible.

Some other way needs to be worked out.

4. One possible way (not the best)

is to use native sql and use something like this (for oracle database)

where Upper(Fieldname) like '%ABC%'

Lets wait for some other better options.

Regards,

Amit M.

Message was edited by: Amit Mittal

[Former Member]

Please ignore this message..........

Message was edited by: Srilatha T

[Former Member]

Hi Daniel,

See the sample code, it is working for me.

Hope this way you will be successful.

   data : itab like hrp1000 occurs 0 with header line,
       v_lines like sy-tabix.
ranges : ls_search for hrp1000-stext.

ls_search-sign   = 'I'.
ls_search-option = 'CP'.
ls_search-low    = '*TEST*'.
append ls_search.
clear ls_search.

ls_search-sign   = 'I'.
ls_search-option = 'CP'.
ls_search-low    = '*test*'.
append ls_search.
clear ls_search.


       select *
         from hrp1000
         into table itab
         where stext in ls_search.

   

Thanks & Regards,

Siri.

[daniel_humberg]

You were right.

If you have several lines in your range table, they are implicitly connected by OR, not by AND.

So, I could do something like this:

ls_search_criterium-sign   = 'I'.
ls_search_criterium-option = 'CP'
ls_search_criterium-low    = '*abc*'.
INSERT ls_search_criterium INTO TABLE ltr_search_criteria.

ls_search_criterium-sign   = 'I'.
ls_search_criterium-option = 'CP'
ls_search_criterium-low    = '*ABC*'.
INSERT ls_search_criterium INTO TABLE ltr_search_criteria.

ls_search_criterium-sign   = 'I'.
ls_search_criterium-option = 'CP'
ls_search_criterium-low    = '*Abc*'.
INSERT ls_search_criterium INTO TABLE ltr_search_criteria.

SELECT * FROM table INTO TABLE lt_table
         WHERE field IN ltr_search_criteria.

I think it is sufficient to search for abc, ABC and Abc, because everything else (aBc, aBC, abC) is almost never done.