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;

3 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

Unknown said...
This comment has been removed by the author.