Solved

Building Dynamic SQL

Posted on 2013-06-05
15
323 Views
Last Modified: 2013-07-16
Hi,

I am building the dynamic SQL query based upon the values which is stored in my custom table. Below is my table structure:

MAIN_CONDITION_NUMBER	SUB_CONDITION_NUMBER	OPERAND1	OPERAND2
1			1.1			DrW1		DrL1
1			1.2			DrW2		DrL2
1			1.3			DrW3		DrL3
1			1.4			DrW4		DrL4

My requirement is to build a SQL like below

AND ((DrW1 = DrL1 OR DrW1 = DrL2 OR DrW1 = DrL3 OR DrW1 = DrL4) OR
	(DrW2 = DrL1 OR DrW2 = DrL2 OR DrW2 = DrL3 OR DrW2 = DrL4) OR
	(DrW3 = DrL1 OR DrW3 = DrL2 OR DrW3 = DrL3 OR DrW3 = DrL4) OR
	(DrW4 = DrL1 OR DrW4 = DrL2 OR DrW4 = DrL3 OR DrW4 = DrL4))	

Open in new window

     
Can anyone provide me the way that how to acheive this? However I can split the data and stored 16 rows instead of 4. But i like this i have many values in table and my table will grow if i split the data and store.

Thanks
0
Comment
  • 7
  • 5
  • 3
15 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
but this is not valid sql...

AND ((DrW1 = DrL1 OR DrW1 = DrL2 OR DrW1 = DrL3 OR DrW1 = DrL4) OR
      (DrW2 = DrL1 OR DrW2 = DrL2 OR DrW2 = DrL3 OR DrW2 = DrL4) OR
      (DrW3 = DrL1 OR DrW3 = DrL2 OR DrW3 = DrL3 OR DrW3 = DrL4) OR
      (DrW4 = DrL1 OR DrW4 = DrL2 OR DrW4 = DrL3 OR DrW4 = DrL4))      

these are valid (pieces):

OPERAND1 = OPERAND2

( OPERAND1 = 'DrW1' AND OPERAND2 = 'DrL1' )

what is it you are really trying to achieve?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
perhaps this is what you are looking for?

and (
         OPERAND1 IN ('DrW1','DrW2','DrW3','DrW4')
       and
         OPERAND2 IN ('DrL1','DrL2','DrL3','DrL4')
      )
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Hi

Thanks for the reply. Actually i am storing the column names in the custom table and fetching that to compare the two columns. Those columns has values in another table.

But my concern is i have to check the condition either one of the above conditions are matching.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
what version of Oracle?
11g has listagg which will do this I believe
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Sorry i am using 10.2.0.3.0.
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 333 total points
Comment Utility
keep in mind, that listagg is limited (if you want to use that for building yout "in-list")... I would rather try to do this via PL/SQL and if you need this later on in pure SQL, make it a pipelined table function, so you can call "select * from table(my_func(param1, param2));"
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
Sorry i am using 10.2.0.3.0.

so, listagg is no option, either way ;-)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
e.g. something along these lines

select
  main_condition_number      
, LISTAGG(OPERAND1   || ' = ' || OPERAND1,' OR' ) WITHIN GROUP (order by OPERAND1)
as relate
from table1
group by main_condition_number

but only for 11g I believe
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
drat
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
If your "base" table is about to grow in terms of rows AND columns, you should also take a look at "method 4 dynamic sql in pl/sql" pattern here:
http://www.oracle-developer.net/display.php?id=422
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
Comment Utility
Hi All,

Instead of Listagg can we handle this in 10g? Is there any similar functionality in 10g same like Listagg? Please provide the alternate way.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
dynamic sql is inherently inefficient (and you may need to guard against sql injection)

could you outline what it is you are trying to achieve?
as noted above listagg is limited, and if you are intending something quite complex string manipulations might not be the bast way.
0
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 333 total points
Comment Utility
Since you obviously need a method to concat rows to a string, please have a look at this site:
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php


@PortletPaul:
dynamic sql is inherently inefficient

Based on what?!?
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 167 total points
Comment Utility
a. it takes some time to build
b. it's not compiled, so it is parsed and an execution/explain plan prepared for each run

i.e. there's an inherent overhead - that's what I meant
that overhead may not be a great deal per instance, but its there

(but of course, you could dynamically build a horrendous query - nothing to stop that from happening either :)
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
Comment Utility
I totally agree with you, Paul, but there are situations when it's inevitable to use dynamic sql. And yes, you have to be very careful using these methods and you should think twice when making the choice :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now