0

My report for the most part works. It is when I select more than one value from either of 2 specific params (@global, @manual) that I get this error:

enter image description here

Here is the SQL:

DECLARE @STATE VARCHAR(2) = 'mn'
    ,@START DATE = '6/1/2020'
    ,@END DATE = '7/1/2020'
    ,@GLOBAL VARCHAR(50) = 'indigent fee'
    ,@MANUAL VARCHAR(100) = '''misc charges'',''discount'''

DROP TABLE

IF EXISTS #customers
    ,#test
    SELECT DISTINCT ch.amount
        ,ch.vehicle_program_id
        ,c.customer_id
        ,ch.customer_charge_id
        ,ch.charge_type
    INTO #customers
    FROM customer c
    JOIN customer_charge ch(NOLOCK) ON c.customer_id = ch.customer_id
    JOIN service_history sh(NOLOCK) ON sh.customer_id = c.customer_id
    JOIN header h(NOLOCK) ON h.service_history_id = sh.service_history_id
    WHERE ch.entry_date BETWEEN @START
            AND @END
        AND ch.price_trigger_id IN (
            16
            ,15
            )
        AND ch.source_type = 1
        AND sh.service_type = 5
        AND h.is_duplicate = 0;

WITH CTE_global
AS (
    SELECT DISTINCT ch.charge_type
        ,'global' AS type
    FROM customer_charge ch
    JOIN store s ON ch.store_id = s.store_id
    JOIN address a ON a.id = s.address_id
    JOIN locality l ON a.locality_id = l.id
    WHERE l.region = @state
        AND ch.price_trigger_id = 16
    
    UNION ALL
    
    SELECT 'None'
        ,'global'
    )
    ,CTE_manual
AS (
    SELECT DISTINCT ch.charge_type
        ,'manual' AS type
    FROM customer_charge ch
    JOIN store s ON ch.store_id = s.store_id
    JOIN address a ON a.id = s.address_id
    JOIN locality l ON a.locality_id = l.id
    WHERE l.region = @state
        AND ch.price_trigger_id = 15
    
    UNION ALL
    
    SELECT 'None'
        ,'manual'
    )
SELECT DISTINCT c.last_name
    ,c.first_name
    ,vp.account_no
    ,cust.charge_type
    ,cust.amount
    ,sh.service_date
    ,s.store_name_short
    ,GLOBAL = g.charge_type
    ,manual = m.charge_type
INTO #test
FROM vehicle_program vp(NOLOCK)
JOIN vehicle v(NOLOCK) ON v.vehicle_id = vp.vehicle_id
JOIN service_history sh(NOLOCK) ON sh.vehicle_program_id = vp.program_id
    AND service_type = 5
JOIN customer c(NOLOCK) ON v.customer_id = c.customer_id
    AND c.customer_id = sh.customer_id
JOIN store s(NOLOCK) ON vp.current_store_id = s.store_id
JOIN #customers cust ON cust.customer_id = c.customer_id
    AND cust.vehicle_program_id = sh.vehicle_program_id
JOIN customer_condition cc(NOLOCK) ON c.customer_id = cc.customer_id
JOIN customer_charge ch(NOLOCK) ON ch.customer_id = c.customer_id
JOIN service_charge sc ON sc.service_history_id = sh.service_history_id
    AND sc.customer_charge_id = cust.customer_charge_id
JOIN header h(NOLOCK) ON h.service_history_id = sh.service_history_id
JOIN CTE_global g ON g.charge_type = ch.charge_type
JOIN CTE_manual m ON m.charge_type = ch.charge_type
WHERE cc.state_of_conviction = @state
    AND sh.service_date BETWEEN @START
        AND @END
    AND h.is_duplicate = 0

SELECT *
FROM #test
WHERE GLOBAL IN (
        CASE 
            WHEN @global IN ('None')
                THEN charge_type
            WHEN @global NOT IN ('None')
                THEN @global
            END
        )
    OR manual IN (
        CASE 
            WHEN @manual IN ('None')
                THEN charge_type
            WHEN @manual NOT IN ('None')
                THEN @manual
            END
        )

For clarity, the last bit in the query there is some logic to allow for these two params to be optional: so by selecting 'None' that param is rendered useless basically. It seems clear that the issue is with this last bit, specifically my WHERE clause using the CASE expression. When I remove that, I don't get the error, but I of course lose my logic. What's most confusing is that the error indicates an issue with a comma, but there's no comma in that part of the SQL?? Any help is going to be greatly appreciated.

Suraj Kumar
  • 5,547
  • 8
  • 20
  • 42
jw11432
  • 545
  • 2
  • 20
  • I think you should assign `@Mannual` variable like `@MANUAL VARCHAR(100) = 'misc charges, discount'`. – Suraj Kumar Jul 06 '20 at 18:29
  • Either formats work, That is just there for debugging. Again, in SSMS there is no problem with the query. I'ts only in SSRS/VS I get the error. – jw11432 Jul 06 '20 at 18:33
  • I am not saying that the query is wrong but it may be a hack for your error just try and see. – Suraj Kumar Jul 06 '20 at 18:35
  • There is no option for me to declare variables in SSRS. That is taken care of by Visual Studio. I have my parameter setup correctly, it pulls from a dataset and I have multi-value allowed. – jw11432 Jul 06 '20 at 18:38

0 Answers0