I have a query like the following:
SELECT A.a, A.b, B.c,
(CASE WHEN ... THEN ... ELSE ... END) AS CalculatedValue,
B.d
FROM dbo.TableA A INNER JOIN
dbo.TableB B ON (...)
WHERE (CASE WHEN ... THEN ... ELSE ... END) BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, (CASE WHEN ... THEN ... ELSE ... END), B.c
to avoid repeating many times the exact same expression: (CASE WHEN ... THEN ... ELSE ... END)
I wanted to define a CTE and query such table using in the select, where and group by the expression CalculatedValue
unfortunately this does not work because the select needs to already include the group by
when creating the CTE
is there any other way I could use to not repeat the CASE WHEN...
so many times?
CTE was introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE a view, as part of the view's SELECT query.
Advantage of Using CTE CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution.
CTEs are usually better when: SQL Server can do a good job of estimating how many rows will come out of it, and the contents of what those rows will be, or. When what comes out of the CTE doesn't really influence the behavior of the rest of the query, or.
Use CROSS APPLY
, which can be used to define aliased fields and then refer to them:
SELECT A.a,
A.b,
B.c,
CalculatedValue,
B.d
FROM
dbo.TableA A
INNER JOIN
dbo.TableB B
ON (...)
CROSS APPLY
(SELECT (CASE WHEN ... THEN ... ELSE ... END)) CxA(CalculatedValue)
WHERE CalculatedValue BETWEEN @DayStart AND @DayEnd
GROUP BY A.a, CalculatedValue, B.c
The CxA
is just an alias and you can name it whatever you like.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With