Need Help On CASE WHEN statement

  • Hey guys,

    I'm working on this query and get errors from my CASE WHEN FUNCTION. I could not figure out why and hope you guys know what problems that I encounter.

    Select

    p21_view_inventory_value_report.[inv_mast_uid]

    ,CASE

    WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) = 0) THEN 'No Usage 9 Months'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc] .stockable = 'N' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)) THEN 'Non-Stockable'

    WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)) THEN 'Temporary'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0) THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)= 0) THEN 'NNU'

    ELSE 'Good' END As 'Status'

    FROM

    [p21_view_inventory_value_report]

    LEFT OUTER JOIN p21_view_inv_loc ON (p21_view_inv_loc.inv_mast_uid = p21_view_inventory_value_report.inv_mast_uid) AND

    (p21_view_inv_loc.location_id = p21_view_inventory_value_report.location_id)

    LEFT OUTER JOIN [p21_inventory_usage_all_view] ON ([p21_view_inventory_value_report].inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND

    [p21_view_inventory_value_report].location_id = [p21_inventory_usage_all_view].location_id)

    LEFT OUTER JOIN p21_view_inv_mast ON p21_view_inventory_value_report.inv_mast_uid = p21_view_inv_mast.inv_mast_uid

    GROUP BY

    p21_view_inventory_value_report.[inv_mast_uid]

    If I took these two CASE WHEN statements out, the query works fine. Therefore, the problem comes from these two queries

    WHEN (SUM (CASE WHEN [p21_view_inv_loc] .stockable = 'N' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)) THEN 'Non-Stockable'

    WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)) THEN 'Temporary'

    I don't know how to fix them

    The errors say : An expression of non-boolean type specified in a context where a condition is expected.

  • Found a space between tablename and the . in the following case statement:

    WHEN (SUM (CASE WHEN [p21_view_inv_loc] .stockable = 'N' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)) THEN 'Non-Stockable'

    Check out the code below for use in your query.

    CASE

    WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period

    BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND

    LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END) = 0) THEN 'No Usage 9 Months'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N'

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END)) THEN 'Non-Stockable'

    WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T'

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END)) THEN 'Temporary'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0)

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END)= 0) THEN 'NNU'

    ELSE 'Good'

    END As 'Status'

  • Thanks for the response. I don't think that made the issue. I tried to copy yours and it is still not working at all. It keeps saying the problem was from the non-boolean type.

  • Took a closer look at the code. You weren't checking for = 0 (hopefully). Compare the following with my previous post:

    CASE

    WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period

    BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND

    LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END) = 0) THEN 'No Usage 9 Months'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N'

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END) = 0) THEN 'Non-Stockable'

    WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T'

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END) = 0) THEN 'Temporary'

    WHEN (SUM (CASE WHEN [p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0)

    THEN [p21_inventory_usage_all_view].actual_usage

    ELSE 0

    END) = 0) THEN 'NNU'

    ELSE 'Good'

    END As 'Status'

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply