0

I have been struggling with this issue for a while and thought I'd ask somewhere. My end goal is an empty text box parameter that allows multiple values... But is completely optional.

I cannot use a drop-down multi-select because I have ~100k possible values.

I have successfully set up a report where users can enter multiple comma-separated values, and it will return results... But they HAVE to enter at least one value.

I have also set up a different report where users can enter just ONE value, or select "Null" and make that parameter completely optional.

I need both at once...

Any tips?

Answered, thanks to Matt!

Query Used (Oracle DB without write permissions):

with temptable1 as
(select :variablename variablename
from random_arbitrary_table
),

temptable2 as
(select distinct trim(regexp_substr(variablename, '[^,]+', 1, level)) as field_name
from temptable1
connect by level <= regexp_count(variablename, '[^,]+')
and prior dbms_random.value is not null
)

select *
from main_query_table mqt
left join temptable2 tt2
on mqt.field_name = tt2.field_name
where ((:variablename is not null and mqt.field_name = tt2.field_name)
or :variablename is null)

-- Set @variablename parameter to 'Allow Null' and default (NULL).

user2907866
  • 1
  • 1
  • 2
    Welcome to stack overflow. Can you provide us some more detail about your parameter configurations and perhaps the SQL code you are passing the parameter to. Because you should be able to pass the comma-separated value or a NULL. Then if you use a split function to split the value to a table then use a LEFT JOIN with a WHERE statement that makes that into an inner join e.g. WHERE (@Parameter IS NOT NULL AND splitvalue = column) OR @Parameter IS NULL – Matt Nov 17 '16 at 22:29
  • 1
    @Matt Thank you! That worked! Used with statements since we do not have write permissions to this Oracle DB... – user2907866 Nov 18 '16 at 19:42

0 Answers0