Solved

Building Dynamic SQL

Posted on 2013-06-05
15
324 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
ID: 39221931
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
ID: 39221949
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
ID: 39221956
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
ID: 39221962
what version of Oracle?
11g has listagg which will do this I believe
0
 

Author Comment

by:Suriyaraj_Sudalaiappan
ID: 39221973
Sorry i am using 10.2.0.3.0.
0
 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 333 total points
ID: 39221975
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]
ID: 39221976
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
ID: 39221983
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
ID: 39221986
drat
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39222026
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
ID: 39223090
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
ID: 39224404
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
ID: 39224898
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
ID: 39224931
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]
ID: 39225058
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle function works in 11g but not in 12c 21 72
Oracle DATE Column Space 11 63
grouping on time windows 6 43
Processing of multiple cursor 6 35
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

12 Experts available now in Live!

Get 1:1 Help Now