Tsql - Use a Derived Select Column in The Where Clause

TSQL - Use a Derived Select Column in the Where Clause

select
    a, b, c
from (
    select
        a, b, c,
        case 
          when a=1 then 5
          when a=2 then 6
        end as d
    from some_table
) as t
where d=6

Use a calculated column in a where clause

Using Derived Columns in a predicate

You'll need to wrap the inner query in a derived table or CTE in order to be able to use derived columns in the WHERE clause (Also, note SUM() is specified just once, using the results of the multiplication):

SELECT x.Code, x.AccountNumber, x.Sales
FROM
(
  SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
  FROM [dbo].Customer c 
      LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
      LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
      LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
  GROUP BY p.Code, c.AccountNumber
) AS x
WHERE x.Sales > 100;

Repeating the Derived Column in a HAVING clause

As per @Jonny's comment, the other way is not to DRY up the calculated column, but to instead repeat the calculation. Use HAVING instead of WHERE after a GROUP BY has been applied.

SELECT p.Code, c.AccountNumber, SUM(p.UnitPrice *od.QtyShipped) AS Sales 
FROM [dbo].Customer c 
  LEFT JOIN [dbo].OrderHeader oh ON oh.CustomerId = c.Id 
  LEFT JOIN [dbo].OrderDetail od ON od.OrderHeaderId = oh.Id
  LEFT JOIN [dbo].Product p ON p.Id = od.ProductId
GROUP BY p.Code, c.AccountNumber
HAVING SUM(p.UnitPrice * od.QtyShipped) > 100;

In either case, as per comments below, note that the calculated expression is SUM(p.UnitPrice * od.QtyShipped) and not SUM(p.UnitPrice) * SUM(od.QtyShipped).

How to filter data in a WHERE clause based on a derived column name?

You cannot refer to an alias from the WHERE directly (you could from the ORDER BY) , you have to use a sub-query or CTE (or repeat the CASE WHEN in the WHERE):

WITH CTE AS
(
    SELECT ActualColumn1, 
    CASE WHEN condition THEN value1 ELSE value2 AS DerivedColumn1
    FROM
    ...
)
SELECT ActualColumn1, DerivedColumn1 
FROM CTE
WHERE DerivedColumn1 <> 'Foo' 

Related: Reference alias (calculated in SELECT) in WHERE clause

Reuse calculated column in WHERE clause

There is no way to reuse the calculated field on the same level SELECT. You will need to nest it in order to use the alias.

SELECT field1
    , calc_field
FROM (
    SELECT field1
        , CONCAT (field2, field3) AS calc_field
    FROM MyTable
    ) tbl
WHERE calc_field LIKE 'A%'

This is because of the order in which clauses are executed in a SQL query. As you can see in the way the clauses are listed, the SELECT clause, where the alias is generated, is executed after the WHERE clause.

Thus, the alias is not "visible" in the WHERE clause, because the alias is generated after the WHERE is applied.

SQL - Derived field in WHERE clause

Stick a

SELECT * FROM (...) WHERE ...

around your query. You can use the aliased columns in your WHERE clause once it's on the inside of the outer SELECT.

Reference alias (calculated in SELECT) in WHERE clause

You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:

SELECT BalanceDue FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;

Or just repeat the expression:

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE  (InvoiceTotal - PaymentTotal - CreditTotal)  > 0;

I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.

PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.

Here are 5 example queries that all yield the exact same execution plan:

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;

SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;

Resulting plan for all five queries:

Can I use a derived column in SQL Server for performing a CASE function?

Please use below query. You cannot use LINKAGE in the case statement as it is a alias and not original database column. You have to use actual column name along with the function instead of alias name

SELECT 
   C1.ID,
   C1.Name,
   COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) AS LINKAGE,
   CASE 
     WHEN COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) LIKE '[A-Z]%' THEN CAST('ACCOUNT' AS 
     varchar(255))
     WHEN COALESCE (C2.ACC_ID, C3.CUSTOMER_ID) LIKE '10%' THEN CAST('CUSTOMER' AS 
     varchar(255))
    ELSE 'Unlinked'
 END AS REL_TYPE

FROM C1

LEFT JOIN C2 ON C1.ID = C2.ID
LEFT JOIN C3 ON C1.ID = C3.ID

Use a calculated field in the where clause

Logically, the select clause is one of the last parts of a query evaluated, so the aliases and derived columns are not available. (Except to order by, which logically happens last.)

Using a derived table is away around this:

select * 
from (SELECT a, b, a+b as TOTAL FROM ( 
           select 7 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 8 as a, 8 as b FROM DUAL 
           UNION ALL 
           select 0 as a, 0 as b FROM DUAL) 
    )
WHERE TOTAL <> 0 
; 

Order by calculated column with alias inside case expression

So while you can use a calculated column in your ORDER BY clause (but not in other clauses such as GROUP BY), you cannot then apply further calculations or conditions - it must be used exactly as created.

There are a whole bunch of ways to solve this problem. Which approach you use will come down to some combination of:

• Which option is clearer to you as the developer
• Which option performs better
• Which option fits into your existing query better

Option 1: Repeat the logic

I don't recommend this option because it violates the DRY principle thereby making it harder to maintain and easier to make mistakes.

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , case
          when @Mode = 0 then 0
          when @Mode = 1 then 1
          when @Mode = 2 then 2
      end as ActiveStudents
from V_SchoolMinimized as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
        end
    end;

The rest of the options are sub-query variations the choice of which comes down to the comments provided as the start.

Option 2: Use a derived table sub-query

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
) as S
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 3: Use a CTE (Common Table Expression)

with cte as (
    select *
        , case
              when @Mode = 0 then 0
              when @Mode = 1 then 1
              when @Mode = 2 then 2
          end as ActiveStudents
    from V_SchoolMinimized
)
select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , S.ActiveStudents
from cte
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then S.ActiveStudents end;

Option 4: Use CROSS APPLY

select top 10
    S.Id as EntityId
    , S.EnglishName as EntityEnglishName
    , S.[Name] as EntityNativeName
    , A.Students
from V_SchoolMinimized as S
cross apply (
    values (
        case
            when @Mode = 0 then 0
            when @Mode = 1 then 1
            when @Mode = 2 then 2
            end
    )
) as A (Students)
order by
    case when @Sort is null then S.Id end
    , case when @Sort = 'engname' then A.Students end;

Note: I suggest keeping your table aliases nice and short, 1-2 characters where possible, occasionally 3.

---

Related Topics