Thursday, August 20, 2009

Dynamic IN clause

Hi ALl,
This is one good ways of writing dynamic in clause..

if you have a select stament where you are uing in clause to restrict the data.if you are not sure on the inclause statment
what you will do..
what i used to do is make a dynamica sql build inclause on run time and then execute it ..But there is another smart way of building it dynamically..

Example1:

cursor c_cust_info is
select * from hz_cust_accounts where customer_class_code in ('CEMP','CEMP20','INDIV')

for making the inclause to by dynamic


cusror c_cust_info(c_in_statement varchar2)
select * from hz_cust_accounts where instr(c_in_statement,customer_class_code)>0

and open the cusor with the in clause you require..like

for rec in c_cust_info('CEMP,CEMP20') loop

end loop;

4 comments:

Anonymous said...

Useful idea !!!

Anonymous said...

Example (simplify) :
SELECT ID
FROM YOUR_TABLE

You want ID 1 and 12
select * from YOUR_TABLE where instr('1,12',ID)>0

It will return ID 1, 12 but also 2.



WHERE
INSTR(
YOUR_DYN_VALUE,
'#'||TO_CHAR(YOUR_TABLE.ID)||';'
)
<> 0

Where your dynamic value looks like
String.Format("(#{0};)", String.Join(";#", _your_list.ToArray()));

=> #1;#12;
It will only return IDs 1 and 12

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...

I am Sridevi Koduru, Senior Oracle Apps Trainer With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K at training@oracleappstechnical.com or sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/