Help with my select statement...

[aris_hidalgo]

Hi guys,

I am trying to modify a part in my program(Dialog programming) in which when a user enters any text in the input box it will search existing records according to what the user typed. for example, I type the letter 'a' and it will search for all records containing the letter 'a'. but my problem is for example, I typed the word 'ACTION' so the search results must only contain the words action but it also outputs other records in which the word 'ACTION' is non existent.Below is the SQL statement that I made:

DATA: V_SEARCH(30) TYPE C, "name of the input box

X_SEARCH(30) TYPE C.

CONCATENATE '%' V_SEARCH '%'

INTO X_SEARCH.

IF V_SEARCH IS INITIAL.

MESSAGE I001.

ELSE.

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES

WHERE TITLE LIKE X_SEARCH

OR

GENRE LIKE X_SEARCH

OR

YEAR_RELEASED LIKE X_SEARCH

OR

CASTS LIKE X_SEARCH

OR

PRODUCER LIKE X_SEARCH

OR

DIRECTOR LIKE X_SEARCH

OR

REVIEW LIKE X_SEARCH

OR

SYNOPSIS LIKE X_SEARCH.

APPEND IT_ZMOVIES.

If guys have a more efficient way to this please let me know and also, how can I do wild card search?

Many thanks guys!

[former_member186741]

I don't think your select will be very efficient but it should 'work'.... are you maybe not clearing the internal table between selects so the table is being appended to each time? Put a refresh in just after the else and before the select.

[former_member186741]

also the append is unnecessary.. the select into table fills the table.

[Former Member]

hi

instead of using variable x_search ..try using direct value ie %search% in the select query..it may help u with selecting all those records having search as the key and also append stat is not necessary!!

plz reward points if it helps!@@

Regards,

Gunjan

[Former Member]

Hi,

You are not using the sy-subrc statement after the select query.

Append statement will append the details in the internal table even if sy-subrc had returned you 4.

It will also increase your program performance in the ABAP side.

cheers,

Maheswaran.B

[sridharreddy_kondam]

HI,

Try using CONDENSE statement for X_Search before the Select statement...

Regards,

Sridhar Reddy k

[Former Member]

try using string relational operators like

CO (Contains Only)

CS (Contains String)

CP (Contains Pattern)

Pls reward points if this helps!!

[aris_hidalgo]

Hi Rajeev,

How can I use relational operators in my statement? I think your suggestion would be very helpful if I get it right. Thanks!

[Former Member]

Hi,

Try and use like this.

declare a range say r_range.

r_range-sign = 'I'.

r_range-option = 'CP'.

r_range-low = 'ACTION'.

append r_range.

and use this in the where condition.

Regards,

GSR.

[Former Member]

Hi

I think u can use the CP as follows:

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES.

if sy-subrc = 0.

sort it_zmovies.

endif.

clear it_zmovies.

loop at it_zmovies.

if it_zmovies-title cs v_search.

---

endif.

Plz reward points by clickin on star if this helps!!

Good Luck!!

Regards,

Rajeev SM

[Former Member]

Hi viray try this one

I understood ur scenario.

<b>I ve used two kind of search one for index search and another for key word search

Index search is used to retrive all the records starting from the alphabet provided by the user.

Keyword search will rerive all records based on the keyword specified by the user.

I used two radiobuttons to select for keyword search and index search.

</b>

REPORT ZSAMPNEW1.

selection-screen begin of block b1 with frame title t1.

<b>Parameters indx radiobutton group g1." index search</b>

parameters alphabet(1) type c.

<b>Parameters key radiobutton group g1." keyword search</b>

parameters keyword(15) type c.

selection-screen end of block b1.

data alphabet1(20).

data keyword1(30).

data it_zmovies like zmovies occurs 0 with header line.

if indx = 'X'.

write: 'index search'.

write:/ .

<b>CONCATENATE alphabet '%'

INTO alphabet1." use only one '%' for index search</b>

write:/ 'list from table for', alphabet.

write:/ .

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES

WHERE TITLE like alphabet1

OR

GENRE LIKE alphabet1

OR

CASTS LIKE alphabet1

OR

PRODUCER LIKE alphabet1

OR

DIRECTOR LIKE alphabet1.

if sy-subrc = 0.

if not it_zmovies[] is initial.

loop at it_zmovies.

write:/ it_zmovies.

endloop.

endif.

endif.

endif.

if key = 'X'.

write: 'keyword serach'.

write:/ .

<b>CONCATENATE '%'keyword '%'

INTO keyword1."use two '%' for keyword search</b>

write:/ 'for the keyword',keyword.

write:/ .

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES

WHERE TITLE like keyword1

OR

GENRE LIKE keyword1

OR

CASTS LIKE keyword1

OR

PRODUCER LIKE keyword1

OR

DIRECTOR LIKE keyword1.

if sy-subrc = 0.

if not it_zmovies[] is initial.

loop at it_zmovies.

write:/ it_zmovies.

endloop.

endif.

endif.

endif.

i got the following results

i.e indexsearch=a

ARMY ACTION TOM BOB WILS

KINGKONG ADVENTURE JILL ERYU ERT

BANED HUMOUR CHARLIE ERT ASD

i.e keyword search=act

ARMY ACTION TOM BOB WILS

ARMAGADON ACTION ARNOLD WERT YERTV

regards

vijay

[Former Member]

hi vir,

I've gone through ur code. some points are

when u have <b>corresponding entries into table opt</b>ion in ur select, u need not again use append clause.

Even though ur code seems to be bit heavy but it works and gives the results as required. i have checked it with all the cases u were talking and got results as expected. the code i tried with ur code only.

PARAMETERS: v_search(30) TYPE c. "name of the input box

DATA: x_search(30) TYPE c.

DATA:

it_zmovies LIKE zmovies OCCURS 0 WITH HEADER LINE.

CONCATENATE '%' v_search '%'

INTO x_search.

IF v_search IS INITIAL.

MESSAGE i001(45).

ELSE.

SELECT *

FROM zmovies

INTO CORRESPONDING FIELDS OF TABLE it_zmovies

WHERE title LIKE x_search OR

genre LIKE x_search OR

year_released LIKE x_search OR

casts LIKE x_search OR

producer LIKE x_search OR

director LIKE x_search OR

review LIKE x_search OR

synopsis LIKE x_search.

ENDIF.

LOOP AT it_zmovies.

WRITE:/ it_zmovies-title.

ENDLOOP.

bye

Lakshmi