topic Help with my select statement... in Application Development and Automation Discussions

Help with my select statement...

aris_hidalgo — Mon, 12 Dec 2005 05:36:08 GMT

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

GENRE LIKE X_SEARCH

YEAR_RELEASED LIKE X_SEARCH

CASTS LIKE X_SEARCH

PRODUCER LIKE X_SEARCH

DIRECTOR LIKE X_SEARCH

REVIEW LIKE X_SEARCH

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!

Re: Help with my select statement...

former_member186741 — Mon, 12 Dec 2005 05:47:57 GMT

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.

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 05:56:06 GMT

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

Re: Help with my select statement...

sridharreddy_kondam — Mon, 12 Dec 2005 05:58:28 GMT

HI,

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

Regards,

Sridhar Reddy k

Re: Help with my select statement...

former_member186741 — Mon, 12 Dec 2005 06:00:10 GMT

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

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 06:09:46 GMT

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

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 06:10:15 GMT

try using string relational operators like

CO (Contains Only)

CS (Contains String)

CP (Contains Pattern)

Pls reward points if this helps!!

Re: Help with my select statement...

aris_hidalgo — Mon, 12 Dec 2005 07:14:28 GMT

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!

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 09:09:30 GMT

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.

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 12:36:34 GMT

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

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 13:26:03 GMT

Hi viray try this one

I understood ur scenario.

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.

REPORT ZSAMPNEW1.

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

Parameters indx radiobutton group g1." index search

parameters alphabet(1) type c.

Parameters key radiobutton group g1." keyword search

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:/ .

CONCATENATE alphabet '%'

INTO alphabet1." use only one '%' for index search

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

write:/ .

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES

WHERE TITLE like alphabet1

GENRE LIKE alphabet1

CASTS LIKE alphabet1

PRODUCER LIKE alphabet1

DIRECTOR LIKE alphabet1.

if sy-subrc = 0.

if not it_zmovies[] is initial.

loop at it_zmovies.

write:/ it_zmovies.

endloop.

endif.

if key = 'X'.

write: 'keyword serach'.

write:/ .

CONCATENATE '%'keyword '%'

INTO keyword1."use two '%' for keyword search

write:/ 'for the keyword',keyword.

write:/ .

SELECT *

FROM ZMOVIES

INTO CORRESPONDING FIELDS OF TABLE IT_ZMOVIES

WHERE TITLE like keyword1

GENRE LIKE keyword1

CASTS LIKE keyword1

PRODUCER LIKE keyword1

DIRECTOR LIKE keyword1.

if sy-subrc = 0.

if not it_zmovies[] is initial.

loop at it_zmovies.

write:/ it_zmovies.

endloop.

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

Re: Help with my select statement...

Former Member — Mon, 12 Dec 2005 14:06:07 GMT

hi vir,

I've gone through ur code. some points are

when u have corresponding entries into table option 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