Reference an alias elsewhere in the SELECT list

by

You can’t refer to an alias outside of SELECT and ORDER BY because of the way a query is parsed. Typical workaround is to bury it in a derived table:

SELECT 
  FirstName, LastName, Other, 
  Flag = CASE WHEN Other IS NOT NULL THEN 1 ELSE 0 END
FROM 
(
  SELECT FirstName, LastName,
    CASE WHEN LastName="Jones"
     THEN 'N/A'
    END AS Other
  FROM dbo.table_name
) AS x;

More Related Contents:

  1. Calculating distance between two points (Latitude, Longitude)
  2. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  3. T-SQL split string based on delimiter
  4. WHERE Clause vs ON when using JOIN
  5. Partition Function COUNT() OVER possible using DISTINCT
  6. CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]
  7. Cannot use UPDATE with OUTPUT clause when a trigger is on the table
  8. CASE WHEN statement for ORDER BY clause
  9. How to get last 7 days data from current datetime to last 7 days in sql server
  10. Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
  11. Using IF ELSE statement based on Count to execute different Insert statements
  12. How to find out what is locking my tables?
  13. Passing dynamic order by in stored procedure
  14. Select SQL Server database size
  15. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  16. How do I find the data directory for a SQL Server instance?
  17. How to return default value from SQL query
  18. How to concatenate all columns in a select with SQL Server
  19. How to automatically generate unique id in SQL like UID12345678?
  20. How do I delete from multiple tables using INNER JOIN in SQL server
  21. Does Foreign Key improve query performance?
  22. SQL : BETWEEN vs =
  23. SQL Server convert string to datetime
  24. Should I design a table with a primary key of varchar or int?
  25. How do I make a composite key with SQL Server Management Studio?
  26. Return number of rows affected by UPDATE statements
  27. Syntax of for-loop in SQL Server
  28. Why does ISNUMERIC(‘.’) return 1?
  29. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  30. How to Select Every Row Where Column Value is NOT Distinct

Leave a Comment