Simplify SQL Query with Variable
I want to simplify my T-SQL query. It goes something like this:
SELECT
t.a AS [Column A],
t.b AS [Column B],
t.c AS [Column C],
(t.a - t.b - t.c) AS [Column D],
CASE
WHEN (t.a - t.b - t.c) = 0 THEN 'Equals'
WHEN (t.a - t.b - t.c) > 0 THEN 'Greater'
WHEN (t.a - t.b - t.c) < 0 THEN 'Less'
END AS [Status]
FROM
Table1 AS t;
It would be nice to put it (t.a - t.b - t.c)
in a variable, so I can use it wherever it happens, because the expression can change over time. I couldn't figure out how to do this without significantly modifying the existing query.
source to share
Use Cross Apply
SELECT
t.a AS [Column A],
t.b AS [Column B],
t.c AS [Column C],
[Column D],
CASE
WHEN [Column D] = 0 THEN 'Equals'
WHEN [Column D] > 0 THEN 'Greater'
WHEN [Column D] < 0 THEN 'Less'
END AS [Status]
FROM
Table1 AS t
CROSS APPLY (SELECT t.a - t.b - t.c AS [Column D]) AS t2
source to share
You can use CTE (Common Table Expression) like this:
;WITH CTE AS
(
SELECT
t.a AS [Column A],
t.b AS [Column B],
t.c AS [Column C],
(t.a - t.b - t-c) AS [Column D]
FROM
Table1 AS t
)
SELECT
[Column A],
[Column B],
[Column C],
[Column D],
CASE
WHEN [Column D] = 0 THEN 'Equals'
WHEN [Column D] > 0 THEN 'Greater'
WHEN [Column D] < 0 THEN 'Less'
END AS [Status]
FROM
CTE
This defines a CTE - something like "ad-hoc" that you can use to handle things like calculations, aggregations, and so on, and then select from it (or use other SQL statements with it). The CTE only exists for one next statement — it is not "stored" for multiple SQL statements. But it's pretty handy for handling situations like this.
source to share
I don't know what your question is. But here's some information.
If you are using SQL Server, you can assign the return of your query to a variable like this:
SELECT @var = value
FROM yourTable
But be careful, if there are multiple lines in the result, only the last line will refer to your variables.
If you just want to save power by using a statement (t.a - t.b - t.c)
, that won't be necessary because SQL Server will evaluate this expression only once and match the data for each WHEN
until it matches.
source to share