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;
Subscribe to:
Post Comments (Atom)
3 comments:
Useful idea !!!
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
Post a Comment