report builder select statement with parameter LOV list (with multiples entries)

Question

i have made a parameter in oracle report builder (p_user) wich can return a list with multiple users.

How can i use this in my select statement to optain the correct list?

I have now:

select note,user,dep_name,dep_id from departement_notes where user=:p_user

For now if i select 1 user it will show all notes from this user. When i select multiple users it will show nothing as the 'user' does not match the p_user value.

Example;

if the paramater 'p_user'= AR then the list shows everything for user AR when the parameter 'p_user'= AR,CZ,GD then nothing is shown

score 0 · Answer 1 · edited May 23 '17 at 10:27

You basically want to parametrize the IN clause, so that it would be user in (:p_user) where p_user is comma-separated list. Unfortunately, it's impossible. There are ways around it, though, see here for example. In your case, it would look like that:

select note,user,dep_name,dep_id 
  from departement_notes    
 WHERE ','||:p_user||',' LIKE '%,'||user||',%'

This way, you search for string ,(username), in the string ,AR,CZ,GD,. Commas are necessary so that you wouldn't match username A. Keep in mind that this approach does not allow you to use indexes on user column, so use carefully. There's another approach described here.

report builder select statement with parameter LOV list (with multiples entries)

1 Answers1