T-SQL Column Alias on Computed Column - Invalid Column Name

Nested computed column Invalid column name error (T-SQL Column alias)

You can't use table aliases in the same select. The normal solution is CTEs or subqueries. But, SQL Server also offers APPLY. (Oracle also supports APPLY and other databases such as Postgres support lateral joins using the LATERAL keyword.)

I like this solution, because you can create arbitrarily nested expressions and don't have to worry about indenting:

SELECT TOP 1000 io.Id, io.QuantityOfProduct, io.Redundant_ProductName,
       io.Order_Id,
       x.FinalPriceForUnit, x.FinalQuantityUnit, x.Final_QuantityAtomic,
       (x.QuantityOfProduct * x.FinalPriceForUnit * x.Final_QuantityAtomic / x.FinalQuantityUnit
       ) as Final_TotalPrice
FROM dbo.ItemInOrder io OUTER APPLY
     (SELECT COALESCE(PriceForUnitOverride, Redundant_PriceForUnit) as FinalPriceForUnit,
             COALESCE(QuantityUnit_Override, Redundant_QuantityUnit) as FinalQuantityUnit
             COALESCE(QuantityAtomic_Override, Redundant_QuantityAtomic) as Final_QuantityAtomic
     ) x
WHERE io.IsSoftDeleted = 0
ORDER BY io.Order_Id ;

Notes:

• I don't find that [ and ] help me read or write queries at all.
• COALESCE() is much simpler than your CASE statements.
• With COALESCE() you might consider just putting the COALESCE() expression in the final calculation.

T-SQL Column alias on computed column - Invalid column name

Column aliases and computations are performed in the projection (SELECT) phase of the query, which occurs after the selection (WHERE and JOIN) phase. Because of this, they can't be referenced in the WHERE clause or in a JOIN condition because they do not yet exist. You can either use your query with the SELECT clause as a subquery or you can duplicate the computation in the WHERE clause:

select * 

from
(select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]) src

where similarity > 2
order by similarity desc

or

select [FirstName], difference([FirstName], 'mitch') as similarity
from [Dev].[dbo].[Name]
where difference([FirstName], 'mitch') > 2
order by similarity desc

Invalid column name (use alias inside where clause)

You need to repeat the CASE statement in the WHERE Clause like this:

WHERE 
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END  BETWEEN 1 AND 100

Alternatively, wrap the original query in an inner query:

SELECT * FROM 
( SELECT
    CASE
       WHEN salePriceDate BETWEEN salePriceStartDate AND salePriceEndDate
          THEN salePrice
          ELSE 
             CASE
                WHEN salePriceDate2 BETWEEN salePriceStartDate2 AND salePriceEndDate2
                   THEN salePrice2
                   ELSE normalPrice
             END
    END AS price
FROM 
    prices
) AS a
WHERE 
    price BETWEEN 1 AND 100

Invalid column name error on SQL statement from OpenQuery results

This should work:

SELECT A.Value
FROM (
SELECT "Ugly OLAP name" as "Value" 
FROM OpenQuery( OLAP, 'OLAP Query')
) AS a
WHERE a.Value > 0

It's not that Value is a reserved word, the problem is that it's a column alias, not the column name. By making it an inline view, "Value" becomes the column name and can then be used in a where clause.

How can I use a calculated field in SQL Server query WHERE condition? (Invalid column name error)

Use like this:

...
AND ABS(DATEDIFF(DAY, GETDATE (), [DataProtocollo]))<15

How can I fix the SQL Query error 'invalid column name'?

You'll need to use a subquery or CTE to first define the QtyDCo column and then in the outer/final query you can reference it.

SQL is defined such that all expressions in the SELECT clause are computed "as if" they're all computed in parallel¹. As such, you can't reference a column that you're also defining in the same SELECT clause.

¹As opposed to e.g. being defined as computed from left-to-right.

SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

I suspect that you have two tables with the same name. One is owned by the schema 'dbo' (dbo.PerfDiag), and the other is owned by the default schema of the account used to connect to SQL Server (something like userid.PerfDiag).

When you have an unqualified reference to a schema object (such as a table) — one not qualified by schema name — the object reference must be resolved. Name resolution occurs by searching in the following sequence for an object of the appropriate type (table) with the specified name. The name resolves to the first match:

• Under the default schema of the user.
• Under the schema 'dbo'.

The unqualified reference is bound to the first match in the above sequence.

As a general recommended practice, one should always qualify references to schema objects, for performance reasons:

• An unqualified reference may invalidate a cached execution plan for the stored procedure or query, since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query. This results in recompilation of the query/stored procedure, a performance hit. Recompilations cause compile locks to be taken out, blocking others from accessing the needed resource(s).

• Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object (that owned by 'dbo'). This is the usual case. The only time a single probe will resolve the name is if the current user owns an object of the specified name and type.

[Edited to further note]

The other possibilities are (in no particular order):

• You aren't connected to the database you think you are.
• You aren't connected to the SQL Server instance you think you are.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

---

Related Topics