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
sakthikumarAsked:
Who is Participating?
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?
0
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?
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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;
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;
0
sdstuberCommented:
what is your output from this line?

              dbms_output.put_line(v_text);
0
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'))
0
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

0
sdstuberCommented:
180000000OPP_SIZE   - that's not a legal identifier.  

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

Check your construction
0
sakthikumarAuthor Commented:
In the database I have for this value OPP_SIZE = '>1000000'

How should I construct / compare in this case
0
Pawan KumarDatabase ExpertCommented:
show us few rows from your table ?
0
sakthikumarAuthor Commented:
Have attached the file, which has screen shot of the rows.
screenshot.png
0
Pawan KumarDatabase ExpertCommented:
Which column value do you want to compare with '>1000000' ??
0
sdstuberCommented:
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.
0
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'
0
sakthikumarAuthor Commented:
condition is put  inside the database column opp_size
0
Pawan KumarDatabase ExpertCommented:
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.
0
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);
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;
0
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

0
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...
0
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.
0
sakthikumarAuthor Commented:
There is no problem in getting the results, only issue is constructing the where clause dynamically.
0
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.
0
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);
commit;

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

insert into tab2 values('> 100');
commit;

declare
	returned_value number;
	v_where_clause varchar2(100);
begin
	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);
end;
/

Open in new window

0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.