How to write dynamic query when conditions are inside the table

There are many examples on the web of dynamic sql.

Before we can offer any suggestions we need to know how you will be executing the query and what you will be doing with the results.

In a nutshell, you build the SQL as a string literal using concatenation then execute the string as SQL.  In SQL/SQL that is normally "execute immediate" or DBMS_SQL procedures.

Then you have to deal with the results.  What do you want to do with the results?

I want to execute the query to get the value out of this conditions, the resulting value is an email distribution list.

This query I have to use in apex to get the results.

The problem I face to use execute immediate is , I have to take out the database value (the condition) using another query to append to this.
Is there any other way?

I don't know APEX so I cannot help with the best way to do it with it.

Use SQL to build SQL.

In PL/SQL it would go something like this:
select 'select email_list from EMAIL_CONFIG_MASTER where :P3_ESTIMATEDTCV ' || OPP_SIZE into yourSQLstring from your_value_table;

Then yourSQLstring should have the valid select.

APEX likely has a better way but you'll still need to pull the values from the table to build the query.

I am trying the below, I am getting invalid relational operator

create or replace procedure abcde is
v_text varchar2(10000);
abc varchar2(5000);

P3_ESTIMATEDTCV number := 180000000;
begin

 
 v_text := 'select email_list                     from EMAIL_CONFIG_MASTER
                    where  (( OPP_SIZE is null) or (OPP_SIZE is not null and '||P3_ESTIMATEDTCV||'OPP_SIZE))                
                    and (Upper(PLATFORM) = upper(''IaaS & Application Development''))';

                              dbms_output.put_line(v_text);
                              execute immediate v_text into abc ;


            dbms_output.put_line(abc);
                              
                              
                              
end;

what is your output from this line?

              dbms_output.put_line(v_text);

select email_list                     from EMAIL_CONFIG_MASTER
                    where  (( OPP_SIZE is null) or (OPP_SIZE is not null and 180000000OPP_SIZE))                
                    and (Upper(PLATFORM) = upper('IaaS & Application Development'))

Please change this line and try again.

v_text := 'select email_list                     from EMAIL_CONFIG_MASTER
                    where  (( OPP_SIZE is null) or (OPP_SIZE is not null and OPP_SIZE = ' || P3_ESTIMATEDTCV || '))                
                    and (Upper(PLATFORM) = upper(''IaaS & Application Development''))';

Select allOpen in new window

180000000OPP_SIZE   - that's not a legal identifier.  

plus you have no conditional operator on it, even if it was.

Check your construction

In the database I have for this value OPP_SIZE = '>1000000'

How should I construct / compare in this case

show us few rows from your table ?

Have attached the file, which has screen shot of the rows.
screenshot.png

Which column value do you want to compare with '>1000000' ??

Take the dynamic part of the problem out.

Write one legal, correct sql statement that produces the results you expect.

Then, identify the parts you need to change.

Post those.

Also, please don't post screenshots of data, those are not usable.
Post text values that can be used to create test cases.

select * from EMAIL_CONFIG_MASTER where 250000 > 1000000 and platform = 'IaaS & Application Development' and sno = 101

select * from EMAIL_CONFIG_MASTER where 250000 <1000000  and platform = 'IaaS & Application Development' and sno = 102

select * from EMAIL_CONFIG_MASTER where 250000 <5000 and platform = 'IaaS & Application Development' and sno = 103

operator and number after operator comes from database eg '> 1000000'

condition is put  inside the database column opp_size

can you pls run below and see what your are getting.

SELECT 'SELECT * FROM EMAIL_CONFIG_MASTER WHERE' || P3_ESTIMATEDTCV || OPP_SIZE || ' and platform = ''IaaS & Application Development'' FROM EMAIL_CONFIG_MASTER

I think this needs to go inside the dynamic part.

I changed it like below, but still getting the same error. I think we need to find a different approach for this problem:)

create or replace procedure abc(P3_ESTIMATEDTCV in varchar2,P_EMAIL_LIST out varchar2) is
v_text varchar2(10000);
v_query_out varchar2(1000);
begin

 
 v_text := 'SELECT (SELECT email_list FROM EMAIL_CONFIG_MASTER WHERE ' || P3_ESTIMATEDTCV ||' OPP_SIZE   and platform = ''IaaS & Application Development'') email_list FROM EMAIL_CONFIG_MASTER';
 


                              dbms_output.put_line(v_text);
                              execute immediate v_text into v_query_out  ;
                    P_EMAIL_LIST := v_query_out;


                              
                              
                              
end;

If the select you want returns more than one row, the procedure will fail.  using execute immediate and selecting INTO a varchar2, the select can only return a single value.


Your select is wrong.  There is no need to select from a select when BOTH of them have "FROM EMAIL_CONFIG_MASTER"

You posted that you want the resulting select to be:
select * from EMAIL_CONFIG_MASTER where 250000 > 1000000 and platform = 'IaaS & Application Development' and sno = 101

This will NEVER return that:

 v_text := 'SELECT (SELECT email_list FROM EMAIL_CONFIG_MASTER WHERE ' || P3_ESTIMATEDTCV ||' OPP_SIZE   and platform = ''IaaS & Application Development'') email_list FROM EMAIL_CONFIG_MASTER';
 

Select allOpen in new window

okay, then what should be my dynamic content,? I hope you got my requirement, should I change the design or how exactly I should build.
-- Any suggestions...

I understand that you want to store your where clauses and selectively choose them at execution time.  Personally, I've never seen this work well whenever I've come across this type of design.

What I don't understand yet is what your results will be and how the application needs to work.  If the result of the query is a list then you might want to return a cursor, XML or JSON.

We cannot answer that for you because we do not know your application and any of its requirements.

There is no problem in getting the results, only issue is constructing the where clause dynamically.

Please provide sample data with create table and insert statements and expected results.

This would be for both the table holding the where clauses and the table you need to query.

Then we can provide 100% tested code.