CODEWITHSUNDEEP
abapopensql
Prashant Bhanawat
Prashant Bhanawat

Reputation: 101

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-a5. Now if check-boxes 1, 3, and 4 are checked then the field of the 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 check-boxes.

And then

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

This becomes difficult if there are 15 check-boxes. Is there a better way to do this?

Upvotes: 2

Views: 12492

Answers (3)

linh
linh

Reputation: 1

one possible way: append checked values to an internal table and then use FOR ALL ENTRIES in select statement.

Upvotes: 0

vwegert
vwegert

Reputation: 18483

Use a select-option/range table for this:

DATA field_range TYPE RANGE OF data_type_of_table_field.

IF p_check1 = abap_true.
  field_range = VALUE #( BASE field_range ( sign = 'I' option = 'EQ' low = 'A1' ) ).
ENDIF.
IF p_check2 = abap_true.
  field_range = VALUE #( BASE field_range ( sign = 'I' option = 'EQ' low = 'A2' ) ).
ENDIF.
" ...

SELECT whatever FROM wherever WHERE field IN field_range.

CAUTION: An empty range table will match anything ("no restrictions") and fetch the entire contents of the database table, so you'll usually need to check for this separately.

Upvotes: 6

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Try like this

select * from table where field IN (a1,a2,a3 ...)

Upvotes: 0

Related Questions