How do you select using optional parameters as filters?

[Former Member]

I am doing it using 2^n SELECT statements where n = number of optional parameters. I think I am doing it wrong. Please help me.

-

---

To illustrate this, I have two optional parameters: param1 and param2. If the user does not input a value in either parameter, it will not be included in the select statement. So I have FOUR select statements to determine the existence of user input.

IF param1 EQ "" AND param2 EQ "".

    SELECT * FROM transparent_table...
    " without filters since param1 and param2 are both blank

ELSEIF param1 NE "" AND param2 EQ "".

    SELECT * FROM transparent_table...
        WHERE field1 = param1
        " using only param1 as filter

ELSEIF param1 EQ "" AND param2 NE "".

    SELECT * FROM transparent_table...
        WHERE field2 = param2
        " using only param2 as filter

ELSEIF param1 NE "" AND param2 NE "".

    SELECT * FROM transparent_table...
        WHERE field1 = param1 AND field2 = param2
        " using both param1 and param2 as filters

ENDIF.

This is still tolerable but the problem obviously appears as the number of optional parameters/select-options increases. Doing this kind of programming, I would have to make 8 SELECT statements for 3 optional parameters, and 16 SELECTs for 4 optional parameters.

Is there a better way to do this?

[Former Member]

I am doing it using 2^n SELECT statements where n = number of optional parameters. I think I am doing it wrong. Please help me.

-

---

To illustrate this, I have two optional parameters: param1 and param2. If the user does not input a value in either parameter, it will not be included in the select statement. So I have FOUR select statements to determine the existence of user input.

IF param1 EQ "" AND param2 EQ "".

    SELECT * FROM transparent_table...
    " without filters since param1 and param2 are both blank

ELSEIF param1 NE "" AND param2 EQ "".

    SELECT * FROM transparent_table...
        WHERE field1 = param1
        " using only param1 as filter

ELSEIF param1 EQ "" AND param2 NE "".

    SELECT * FROM transparent_table...
        WHERE field2 = param2
        " using only param2 as filter

ELSEIF param1 NE "" AND param2 NE "".

    SELECT * FROM transparent_table...
        WHERE field1 = param1 AND field2 = param2
        " using both param1 and param2 as filters

ENDIF.

This is still tolerable but the problem obviously appears as the number of optional parameters/select-options increases. Doing this kind of programming, I would have to make 8 SELECT statements for 3 optional parameters, and 16 SELECTs for 4 optional parameters.

Is there a better way to do this?

[Former Member]

I can think of 3 ways to achieve this - there are probably more... have a look at the demo code below.

Jonathan

report zsd_jc_dynamic_select.
tables:
  t001.
parameters:
  p_parm1               type t001-bukrs,
  p_parm2               type t001-waers.
select-options: "Option 3 - make Sel Opt look like a single param
  s_tip3                for t001-spras no-extension no intervals.
start-of-selection.
  perform demo_code.

form demo_code.
  data:
    l_where             type text72.
  ranges:
    lr_bukrs            for t001-bukrs,
    lr_waers            for t001-waers.
*
* Option 1 - range tables
  if not p_parm1 is initial.
    clear lr_bukrs.
    lr_bukrs-sign   = 'I'.
    lr_bukrs-option = 'EQ'.
    lr_bukrs-low    = p_parm1.
    append lr_bukrs.
  endif.

  if not p_parm2 is initial.
    clear lr_waers.
    lr_waers-sign   = 'I'.
    lr_waers-option = 'EQ'.
    lr_waers-low    = p_parm2.
    append lr_waers.
  endif.

  select *
    from t001
    where bukrs in lr_bukrs
    and   waers in lr_waers
    and   spras in s_tip3.
    write: / 'Company', t001-bukrs, 'Curr', t001-waers, 'Land', t001-land1.
  endselect.
*
* Option 2 - dynamic where
  uline.
  if not p_parm1 is initial.
    if not l_where is initial.
      concatenate l_where 'AND~' into l_where separated by space.
    endif.
    concatenate
      l_where
      'BUKRS =~' '''' p_parm1 ''''
      into l_where.
    replace 'AND~' with 'AND ' into l_where.
    replace '=~' with '= ' into l_where.
  endif.

  if not p_parm2 is initial.
    if not l_where is initial.
      concatenate l_where 'AND~' into l_where separated by space.
    endif.
    concatenate
      l_where
      'WAERS =~' '''' p_parm2 ''''
      into l_where.
    replace 'AND~' with 'AND ' into l_where.
    replace '=~' with '= ' into l_where.
  endif.

  select *
    from t001
    where (l_where)
    and   spras in s_tip3.
    write: / 'Company', t001-bukrs, 'Curr', t001-waers, 'Land', t001-land1.
  endselect.

endform. 

[Former Member]

Thanks Jonathan,

Actually, after much thinking after already posting my question, I have already thought of transforming the parameters into select options (ranges). Neverthless, great ideas! I'll keep them in mind.

Kyle

[Former Member]

I would prefer the orginal option !!! No range, no dynamic coding, just plain written OPEN SQL !!! Readability and correctness of the code will be much better. Performance is equal as the code which goes to the database is identical.

[Former Member]

Sorry... Deleted be me, because the cuestion is really answered...

Edited by: Diego Alvarez on Jan 27, 2010 11:30 AM