passing multiple parameters from workbook to session variable [message #186462] |
Tue, 08 August 2006 01:26 |
crmoffat
Messages: 33 Registered: November 2005 Location: Australia
|
Member |
|
|
Hi all
quite often i use the technique of setting a session variable from a workbook (through a function) to filter views that the workbook uses. For example a function called 'set_effective_date' that can be called from a workbook and sets a session variable v_effective_date (and get_effective_date to return it) I then have views with sql like
select tb.*
from table tb
where get_effective_date between tb.effective_start_date and tb.effective_end_date
all reasonable straight forward. However, I now need to do something similar with a parameter than can have multiple parameters. For example with 'Parameter1' a user can select many values, eg 'V1', 'V2' and 'V3' and so on. What I need to do create a function to capture these values, write them to a temp table or object, then use them to create the following select (example only)
select tb.*
from table tb
where tb.value in (select * from temp_table)
note: where temp_table will have the values 'V1', 'V2' and 'V3'
Can somebody give me some idea how to go about this? I can't really determine how discoverer is passing back these values to the database (as the show sql only says value = :parameter1 which doesn't help a lot). If somebody could shed some light on that matter I would appreciate that as well.
cheers
cameron
p.s. please let me know if any extra info would be helpful to answer these questions
|
|
|