Avoid Repeated Case When

Question

How can I avoid having to type the same CASE WHEN... over and over in a WHERE clause?

CASE WHEN rawGlass.blockHeight > rawGlass.blockWidth THEN rawGlass.blockWidth ELSE rawGlass.blockHeight END repeats quite frequently in this query. I'd like to only have to do that once. I already know referencing [Glass Height] in the WHERE clause will result in an Invalid column... error.

I realize there may be other solutions that don't involve using a CASE WHEN that will work for this particular instance and I welcome them, but I would really like an answer to the question at hand since I've come across this problem before with other queries.

SELECT
    parts.pgwName AS [Part Name],
    parts.active AS [Active],
    cutVinyl.boardName AS [Vinyl Board],
    cutVinyl.rollWidth AS [Roll Width],
    CASE WHEN rawGlass.blockHeight > rawGlass.blockWidth THEN rawGlass.blockWidth ELSE rawGlass.blockHeight END AS [Glass Height]
FROM
    parts
        LEFT JOIN cutVinyl ON parts.vinylBoard = cutVinyl.boardName
        LEFT JOIN rawGlass ON parts.outerSku = rawGlass.sku
WHERE
    (
        (cutVinyl.stretchRadius IS NOT NULL OR cutVinyl.stretchRadius = 0) AND 
        cutVinyl.rollWidth < (CASE WHEN rawGlass.blockHeight > rawGlass.blockWidth THEN rawGlass.blockWidth ELSE rawGlass.blockHeight END)
    ) ...

Note: The list of conditions goes on a while, so wrapping the whole WHERE clause in one CASE WHEN... statement and then repeating the conditions twice doesn't really help any.

Answer 1

Use CROSS APPLY

SELECT
    pgwName AS [Part Name],
    active AS [Active],
    boardName AS [Vinyl Board],
    rollWidth AS [Roll Width],
    [Glass Height]
FROM
    parts
        LEFT JOIN cutVinyl ON parts.vinylBoard = cutVinyl.boardName
        LEFT JOIN rawGlass ON parts.outerSku = rawGlass.sku
        CROSS APPLY (
            SELECT CASE WHEN blockHeight > blockWidth THEN blockWidth 
                                                      ELSE blockHeight 
                   END AS [Glass Height]
        ) AS CA1
WHERE
    (
        (stretchRadius IS NOT NULL OR stretchRadius = 0) AND 
        rollWidth < [Glass Height])
    ) ...