Solved

Building Dynamic SQL

Posted on 2013-06-05
15
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 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