Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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?

>Solution :

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading