Attempting to use result of Case Expression in a join .. need to improve query-CodePudding

I have the following query which allows me to join the TransactionClass tables base on TransactionClassID from either the primary table (Transactions) or TransactionRules based on a condition as below:

SELECT
    Description,
    TC.Name,
    (CASE
         WHEN (TR.TransactionRuleId > 0)
         THEN TR.TransactionRuleId
         ELSE T.TransactionClassId
    END) As ClassId
FROM Transactions AS T
LEFT JOIN TransactionRules TR ON T.Description LIKE TR.Pattern
LEFT JOIN TransactionClasses TC ON TC.TransactionClassId =
                                (CASE
                                    WHEN (TR.TransactionRuleId > 0)
                                    THEN TR.TransactionClassId
                                    ELSE T.TransactionClassId
                                END)

The query is running on SQL Server, In effect, it retrieves the correct TransactionClass entry depending on whether or not the join on TransactionRules was successful or not. The above query works, but I am trying to simplify the query so that I do not have to repeat the CASE expression in two places.

I attempted to capture the result of the case expression in a variable and use that as follows:

SELECT
    Description,
    x
FROM Transactions AS T
LEFT JOIN TransactionRules TR
    ON T.Description LIKE TR.Pattern
LEFT JOIN TransactionClasses TC
    ON TC.TransactionClassId = x
WHERE x = (CASE
               WHEN (TR.TransactionRuleId > 0)
               THEN TR.TransactionRuleId
               ELSE T.TransactionClassId
           END)

But I get the error:

[S0001][207] Line 8: Invalid column name 'x'.

Where am I going wrong in my attempt to have only one CASE Expression?

CodePudding user response：

CROSS APPLY is a tidy way to reuse a calculated value e.g.

SELECT
    [Description]
    , TC.[Name]
    , Class.Id
FROM Transactions AS T
LEFT JOIN TransactionRules TR ON T.[Description] LIKE TR.Pattern
CROSS APPLY (
    VALUES (
        CASE
           WHEN TR.TransactionRuleId > 0
           THEN TR.TransactionRuleId
           ELSE T.TransactionClassId
        END
    ) 
) AS Class (Id)
LEFT JOIN TransactionClasses TC ON TC.TransactionClassId = Class.Id;