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
Oracle Database

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
slightwv (䄆 Netminder)

Please provide some sample data and expected results for Table_A?

Are you wanting sort of a UNION type query result?
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
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
alines

ASKER
Hi slightwv

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

Thanks
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

>>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.