Cascading non-mandatory input control to a mandatory input control

0

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 mandatory
SELECT DISTINCT category
FROM dim_brief_subcategory_category
WHERE category <> 'Unknown'
ORDER BY category
 
-- query for brief input
-- multi select query
-- mandatory
SELECT brief_key, brief_name 
FROM sb_dwh.dim_brief b
WHERE b.brief_activestatus LIKE 'A%' 
AND b.brief_key <> -999
AND b.is_main = 1
AND b.is_dedicated_send = 0
AND 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}
) END
ORDER BY b.brief_name

lisaws's picture
67
Joined: Oct 16 2015 - 10:14am
Last seen: 1 month 2 weeks ago

0 Answers:

No answers yet
Feedback