topic Re: sql select statement with field having multiple values in Application Development and Automation Discussions

sql select statement with field having multiple values

Former Member — Wed, 30 Mar 2016 06:03:44 GMT

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.

Re: sql select statement with field having multiple values

RaymondGiuseppi — Wed, 30 Mar 2016 06:19:50 GMT

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

Re: sql select statement with field having multiple values

Former Member — Wed, 30 Mar 2016 11:37:53 GMT

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