3

I want to simplfy my T-SQL query. It is 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 (t.a - t.b - t.c) into a variable, so I can reuse it on all places it occurs, because the expression may change over time. I could not figure out how to do this without changing the existing query significantly.

Improve this question
0

3 Answers 3

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

you need to name the cross applied "table"
How do I need to name the cross applied table ?
OK, I will try this solution and give a feedback
1

You can use a 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 a "ad-hoc" view - that you can use to handle things like calculations, aggregations etc. and then select from it (or use other SQL statements against it). The CTE only exists for the one, next statement - it doesn't get "persisted" for multiple SQL statements. But it's quite handy to handle situations like this

Improve this answer

4 Comments

If he uses SQL Server it won't be necessary as the expression (t.a - t.b - t.c) just evaluated once. The query will perform the same. By the way, you have an error in your code. Not (t.a - t.b - t-c) it should be (t.a - t.b - t.c).
@Ionic: yes, it will perform the same - but it's easier to read with a CTE, in my opinion
yes this might be. But if there are further joins later on maybe harder to debug and every developer should understand what a CTE is. The original query is just basic SQL which every developer should understand. :-)
Using the CTE seems like duplicating the code and that would be even more less maintainable.
-1

I don't know what's your question. But here is some information for you.

If you use SQL Server, you can assign the returns of your query to a variable like that:

SELECT @var = value
FROM yourTable

But beware, if you have multiple rows in the result, only the last row will be taken to your variables.

If you just want to save power with your (t.a - t.b - t.c) statement, it won't be necessary as SQL Server will evaluate those expression just one time and make the data compare for each WHEN until one matches.

Improve this answer

2 Comments

No, I don't want to save power, I just want to make my Code more maintainable.
Well it's quite simple. I won't use CTE's or anything else to it. As if your developer were not so experienced they won't know how to fix errors in it. And as I think, due to this question, they aren't all experienced?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.