Solved

Building Dynamic SQL

Posted on 2013-06-05
15
325 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
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: 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
 
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

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.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

786 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