Skip to Content
avatar image
Former Member

sql select statement with field having multiple values

i have few check-boxes on my selection screen.Each check-box corresponds to a value which a field of a table can take.I have to then fire a select query where a particular field of a table can have all values whose corresponding check-box is selected.

Suppose i have 5 check-box corresponding to values a1,a2 till a5.

Now if check-box 1 ,3 and 4 is checked then the filed of table can have values a1 or a3 or a4.

select * from table where field = a1 or field = a2 or field = a3.

One way to do this is creating 5 variables and then doing something like this

if checkbox1 checked

then var1 = a1

else

var1 = '0' //something which would never occur in the field of the table

and so on for all checkboxes.

and then

select * from table where field = var1 or field = var2 or field = var3 or field = var4 or field = var5.

Is there a better way to do this.

Thanks.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 30, 2016 at 06:19 AM

    Define a TYPE RANGE data and fill it with the checked values

    DATA: my_range TYPE RANGE OF my_element.

    REFRESH my_range.

    IF checkbox1 IS NOT INITIAL.

    APPEND INITIAL LINE TO my_range ASSIGNING <value>.

    <value>-sign = 'I'.

    <value>-option = 'EQ'.

    <value>-low = field1.

    ENDIF.

    SELECT * FROM my_table WHERE field IN my_range.

    You could also use some dynamic assignment to checkbox and value with field symbols (or the obsolete DO VARYING for the nostalgics only.)

    Regards,

    Raymond

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 30, 2016 at 11:37 AM

    Hi Gaurav,

    As you have explained, If values(a1,a2,..a5) are entered by user on selection screen in that case you can go with select options. For select-options you can hide intervals and button using some extensions for select-options.

    If these values are constants, in that case you can create ranges and populate it with values.

    Once you have select-option/ranges with you, you can hit select statement where you need to keep all fields with "AND" conditions.

    Another way you can solve this problem is by dynamic query. In that case you create clause after "WHERE" in select statement in one string(Say str_whr) and use it in select statement(SELECT * FROM xyz WHERE (str_whr). here str_whr = 'field1 = a1 or field3 = a3 or field4 = a4')

    Thanks,

    Hardik

    Add comment
    10|10000 characters needed characters exceeded