How to write dynamic query when conditions are inside the table

Hi Experts,

Please check the below query in which the value of :P3_ESTIMATEDTCV  will be a numeric and the
OPP_SIZE  is stored inside database it contains a check like this '>100'

I need to execute the below statement,  how to do this.?

select email_list                     from EMAIL_CONFIG_MASTER
                    where :P3_ESTIMATEDTCV || OPP_SIZE
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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?
sakthikumarAuthor Commented:
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?
slightwv (䄆 Netminder) Commented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sakthikumarAuthor Commented:
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;

 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''))';

                              execute immediate v_text into abc ;

what is your output from this line?

sakthikumarAuthor Commented:
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'))
Pawan KumarDatabase ExpertCommented:
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''))';

Open 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
sakthikumarAuthor Commented:
In the database I have for this value OPP_SIZE = '>1000000'

How should I construct / compare in this case
Pawan KumarDatabase ExpertCommented:
show us few rows from your table ?
sakthikumarAuthor Commented:
Have attached the file, which has screen shot of the rows.
Pawan KumarDatabase ExpertCommented:
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.
sakthikumarAuthor Commented:
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'
sakthikumarAuthor Commented:
condition is put  inside the database column opp_size
Pawan KumarDatabase ExpertCommented:
can you pls run below and see what your are getting.


I think this needs to go inside the dynamic part.
sakthikumarAuthor Commented:
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);

 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';

                              execute immediate v_text into v_query_out  ;
                    P_EMAIL_LIST := v_query_out;

slightwv (䄆 Netminder) Commented:
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';

Open in new window

sakthikumarAuthor Commented:
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...
slightwv (䄆 Netminder) Commented:
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.
sakthikumarAuthor Commented:
There is no problem in getting the results, only issue is constructing the where clause dynamically.
slightwv (䄆 Netminder) Commented:
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.
slightwv (䄆 Netminder) Commented:
Here is a very simplified test case that shows what I think you are trying to do:
drop table tab1 purge;
create table tab1(col1 number);

insert into tab1 values(1);
insert into tab1 values(101);

drop table tab2 purge;
create table tab2(where_clause varchar2(100));

insert into tab2 values('> 100');

	returned_value number;
	v_where_clause varchar2(100);
	select where_clause into v_where_clause from tab2;

	execute immediate 'select col1 from tab1 where col1 ' || v_where_clause into returned_value;
	dbms_output.put_line('I got: ' || returned_value);

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.