Link to home
Start Free TrialLog in
Avatar of alines
alines

asked on

Dynamic Where Statement

Hi

Looking to build a pipeline function that can dynamically add 'exists' and 'not exists' statements to the sql script depending on what the users have included in a filter table.

The filter tables will be constructed in a similar way

Field Name             Operator          Value    
Field_A                    =                       A
Field_A                    =                       B
Field_A                    !=                      C
Field_B                    !=                      X

Select
  Field_A,
  Field_B
from table_A a
where
exists (Select 1 from filter_table ft
           where ft.field_name = 'Field_A'
           and operator = '='
           and a.field_a = ft.value)
and not exists (Select 1 from filter_table ft
           where ft.field_name = 'Field_A'
           and operator = '!='
           and a.field_a = ft.value)
and not exists (Select 1 from filter_table ft
           where ft.field_name = 'Field_B'
           and operator = '!='
           and a.field_b = ft.value)

Thanks in advance
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Please provide some sample data and expected results for Table_A?

Are you wanting sort of a UNION type query result?
Avatar of alines

ASKER

Hi

Not really a UNION.  looking for the 'exists' and 'not exists' elements of the where clause to be dynamically added depending on the results in the filter_table.  Users will add and remove values to the filter table which I'm looking to automatically adjust the where clause in the function.

Table A will look like this

Table_A
Field_A         Field_B
A                   R
B                   T
A                   Q
A                   X

So the only values I would expect to come back are
Field_A         Field_B
A                   R
B                   T
A                   Q

Though the users could then edit the filter table above and remove the values
Field Name             Operator          Value
Field_B                    !=                      X

So the next time the function is run the following part of the where statement is removed
and not exists (Select 1 from filter_table ft
           where ft.field_name = 'Field_B'
           and operator = '!='
           and a.field_b = ft.value)
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of alines

ASKER

Hi slightwv

Do you know the best way of using this in a pipelined function ?

Thanks
>>Do you know the best way of using this in a pipelined function ?

Use the generated SQL to open a cursor.  Then loop through it and pipe the rows.