October 26, 2014 at 9:05 pm
Hi Everyone. Please see the following Code:
SELECT
CASE
WHEN SUBSTRING(mat.Code,1,1) = 'C' THEN 'Copper'
WHEN SUBSTRING(mat.Code,1,1) = 'L' THEN 'Limestone'
WHEN mat.Code IN (
(SELECT
a.Code
FROM [Material] a
INNER JOIN [Material] b
ON a.ParentMaterialID = b.MaterialID
WHERE b.Code IN ('AU','CU_WA','AU_WA'))
OR mat.Code = 'PYRT'
THEN "Waste'
WHEN SUBSTRING(mat.Code,1,2) = 'AU' THEN 'Gold'
END AS Material
From Table1 mat
I have other Fields/Colums in this SELECT statement that I am not showing in this example to keep the example simple. Some of those columns have SUM Aggregates which mean I have to include the above CASE Statement in the Group by Clause. However Management Studio doesn't allow Subqueries in Group By Clause. That is , My CASE Statement has a a Subquery in it.. Does anyone please have a work around for this issue??
Thanks Everyone
October 27, 2014 at 12:23 am
Quick thought, embed the case statement in a CTE and then do the aggregations on the output.
😎
October 29, 2014 at 6:32 am
Without more details of the overall query, it's rather difficult to know what will work vs. what won't. Obviously, you're not going to be able to have a sub-query within an aggregate, so what you may need to do is have an overall sub-query that can get this CASE statement out of the way at the sub-query level ? The idea being not dis-similar to what your CASE statement sub-query does - establish a parent-child relationship. I'm not sure what that would look like. One other alternative would be to establish ALL the possible values for mat.Code and the corresponding values for the CASE statement result and place that into a CTE that can then JOIN directly to Table1.Code and use that as your means of providing the result. I think the latter is the more practical solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 29, 2014 at 6:17 pm
Thanks very much for the help. I really appreciate it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply