Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Building Dynamic SQL

Posted on 2013-06-05
15
Medium Priority
?
330 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 49

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 49

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 49

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 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 999 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 14

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 49

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 49

Expert Comment

by:PortletPaul
ID: 39221986
drat
0
 
LVL 14

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 49

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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 999 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 501 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 14

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

661 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