Dynamic Where Statement

alines
alines used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Please provide some sample data and expected results for Table_A?

Are you wanting sort of a UNION type query result?

Author

Commented:
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)
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
This should generate the SQL you posted above.  You can use it as the code stub to complete your pipelined function.

drop table tab1 purge;
create table tab1(field_name varchar2(10), operator varchar2(10), value varchar2(10));
insert into tab1 values('Field_A','=','A');
insert into tab1 values('Field_A','=','B');
insert into tab1 values('Field_A','!=','C');
insert into tab1 values('Field_B','!=','X');
commit;

drop table tab2 purge;
create table tab2(field_a char(1), field_b char(1));

insert into tab2 values('A','R');
insert into tab2 values('B','T');
insert into tab2 values('A','Q');
insert into tab2 values('A','X');
commit;

declare
	mySQL varchar2(32000) := 'select field_a, field_b from tab2 a where 1=1 and (';
begin
	for i in (select field_name, operator, value from tab1) loop
		mySQL := mySQL || case when i.operator = '=' then 'exists' else 'not exists' end ||
			'( select 1 from tab1 ft where ft.field_name=''' || i.field_name || ''' and operator=''' || i.operator || ''' and a.' || i.field_name || ' = ft.value) and ';
	end loop;
	mySQL := substr(mySQL,1,length(mySQL)-4) || ')';

	dbms_output.put_line(mySQL);
end;
/

Open in new window

Author

Commented:
Hi slightwv

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

Thanks
Most Valuable Expert 2012
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial