troubleshooting Question

Dynamic Where Statement

Avatar of alines
alines asked on
Oracle Database
5 Comments1 Solution366 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros