Dynamically frame Filter condition based on conditions

We have separate table maintained for condition / filters. Based on the conditions, filters to be applied at base table.

here’s the sample input conditional data for reference purpose

+-----------+-------------------+----------+------------+--------------+---------------+----------------+
val_range   |val_range_operator | val_From |  val_till  |   val_except  |  except_from |   except_till  |
+-----------+-------------------+----------+------------+---------------+--------------+----------------+
            |                   |          |            |               |              |                |
100         |  =                |          |            |               |              |                |
            |                   |          |            |               |              |                |   
            | between           |   200    |    300     |               |              |                |
            |                   |          |            |               |              |                |
            | between           |   410    |    620     | 450,600,610   |              |                |
            |                   |          |            |               |              |                |
            | between           |   800    |   999      | 810,820,850   |     890      |     930        |
            |                   |          |            |               |              |                |
            | between           |   1200   |   1500     |               |     1300     |      1399      |
+-----------+-------------------+----------+------------+---------------+--------------+----------------+

Based on this input conditions , filter to be derived as follows.

Select col* 
from transaction_tbl 
where 
    val_range = 100 
    OR val_range between 200 AND 300 
    OR val_range between 410 AND 620 AND val_range not in (450,600,610) 
    OR val_range between 800 AND 999 AND val_range not in (810,820,850) AND NOT BETWEEN 890 and 930 
    OR val_range between 1200 AND 1500 AND val_range NOT BETWEEN 1300 AND 1399 

please help me in achieving the filter query

Answer

The below sparkSQL will help you to build the where clause,

select 
  concat(
    '( ', 
    concat_ws(
      ') OR (', 
      collect_list(
        case when val_range_operator = '=' 
        and val_range is not null then concat_ws(' ', 'val_range', '=', val_range) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is null 
        and except_from is null 
        and except_till is null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is not null 
        and except_from is null 
        and except_till is null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND', 'val_range', 
          'NOT', 'IN', '(', val_except, ')'
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is not null 
        and except_from is not null 
        and except_till is not null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND', 'val_range', 
          'NOT', 'IN', '(', val_except, ')', 
          'AND NOT BETWEEN', except_from, 
          'AND', except_till
        ) when val_range_operator = 'between' 
        and val_From is not null 
        and val_till is not null 
        and val_range is null 
        and val_except is null 
        and except_from is not null 
        and except_till is not null then concat_ws(
          ' ', 'val_range', 'between', val_From, 
          'AND', val_till, 'AND NOT BETWEEN', 
          except_from, 'AND', except_till
        ) end
      )
    ), 
    ' )'
  ) as filter_condition 
from 
  filter_tb

PS: scenarios are derived based on reference data. Please update the query if any other scenario exists.