I am writing a query where 'batch_name' is the parameter, some times I get only one batch name and sometime I get 2 or more batch names. How can I handle this in Oracle BI Publisher query,
Here is my query,
Select * from pay_batch_headers pbh Where UPPER(pbh.batch_name) = UPPER(:p_batch_name)
Now this query will handle for only one batch name, I want it to handle multiple batch names.
something like Where UPPER(pbh.batch_name) IN ('Batch1','Batch2','Batch3')
But problem to use IN clause is I cant predict number of batches I have to query. Can any one help me in this please.
You have two choices. One is to munge the variables together into a string and use some method, such as regexp_like():
where regexp_like(upper(pbh.batch_name), ??)
The parameter string should look like: '^abc|def|ghi|jkl$'. You can make it as long as you like.
Another method is to use execute immediate. Dump the values into a SQL query as a string, using IN. The advantage of this method is that it can more easily use indexes