0

is it possible to use the select statement result as where clause statement/Condition in oracle SQL? Eg:

Condition_txt(FIELD) = 'AND 
b.mpal03d >= TO_DATE('01/10/2012','DD/MM/YYYY')' 
(Value of field)

1 Answer 1

0

Yes, but you'll need dynamic SQL.

Here's an example.

This is result we expect:

SQL> select count(*)
  2  from emp
  3  where hiredate >= to_date('01/12/1981', 'dd/mm/yyyy');

  COUNT(*)
----------
         5

Table that contains conditions; I set it to one condition per table as I don't know how it actually looks like in your case:

SQL> select * From filter;

TAB CONDITION
--- ----------------------------------------------------
EMP  and hiredate >= to_date('01/12/1981', 'dd/mm/yyyy')

Code:

SQL> set serveroutput on
SQL> declare
  2    l_str         varchar2(500);
  3    l_table_name  filter.table_name%type := 'EMP';
  4    l_condition   filter.condition%type;
  5    l_cnt         number;
  6  begin
  7    select f.condition
  8      into l_condition
  9      from filter f
 10      where f.table_name = l_table_name;
 11
 12    l_str := 'select count(*) from ' || l_table_name ||
 13             ' where 1 = 1 ' || l_condition;
 14
 15    execute immediate l_str into l_cnt;
 16
 17    dbms_output.put_line('Number of rows = ' || l_cnt);
 18  end;
 19  /
Number of rows = 5

PL/SQL procedure successfully completed.

SQL>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.