Cascading non-mandatory input control to a mandatory input control

[lisaws]

Hi,

I am having trouble with a report that has a non-mandatory input which cascades to a mandatory input.  In MySQL the report runs perfectly fine, but once I converted it to MS SQL Server and also to Snowflake (which is ANSI SQL), I get the following errors:

MS SQL Server: "java.sql.SQLException: Unable to convert between java.util.Collections$EmptyList and JAVA_OBJECT."

Snowflake: "net.snowflake.client.jdbc.SnowflakeSQLException: Data type not supported for binding: Object type: class java.util.Collections$EmptyList."

For this report the user is required to select a newsletter aka brief, but they can opt to first select an industry.  If they do, the list of newsletters should be limited to only that industry.

--query for industry input: industry_multi_opt--multi select query--not mandatoryselect distinct categoryfrom dim_brief_subcategory_categorywhere category <> 'Unknown'order by category-- query for brief input-- multi select query-- mandatoryselect brief_key, brief_name from sb_dwh.dim_brief bwhere b.brief_activestatus like 'A%' and b.brief_key <> -999and b.is_main = 1and b.is_dedicated_send = 0and case     when $P{industry_multi_opt} is null then 1=1     else b.brief_key in (        select brief_key        from link_brief_subcategory_category  l        inner join dim_brief_subcategory_category c on l.brief_subcategory_category_key = c.brief_subcategory_category_key        where $X{IN,c.category, industry_multi_opt}) endorder by b.brief_name[/code]