July 9, 2012 at 10:06 am
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.
July 9, 2012 at 10:35 am
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'
July 9, 2012 at 10:41 am
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.
July 9, 2012 at 10:48 am
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