Reference an alias elsewhere in the SELECT list

mysql use alias in same select
sql reference previous column in select
sql use alias in select
reference alias in where clause
postgres reuse alias in select
postgres reference column alias in where clause
mysql select alias reuse
postgres alias select

I have the following query:

    select FirstName, LastName,
    Case
     When LastName = 'Jones'
     then 'N/A'
    End as Other,
    Case 
      When Other is not null 
      then 1 
    else 0 as Flag

The Flag value depends on Other but as Other is an alias, the Flag field does not recognize Other.

I guess, I can use a select within a select. Is there a better way for Flag to recognize the Other alias column?

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;

Alias (SQL), or table. This is often useful when either tables or their columns have very long or complex names. 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;

You can also do this with a common table expression (CTE):

;WITH cte AS 
(
SELECT 
    firstname, 
    lastname, 
    CASE WHEN lastname = 'Jones' THEN 'N/A' 
    END AS Other 
FROM @t
) 
SELECT 
    firstname, 
    lastname, 
    CASE WHEN other IS NOT NULL THEN 1 
        ELSE 0 
    END AS Flag 
FROM cte

How do i use alias in where clause?, is evaluated, the column value may not yet have been determined. Optionally, you can define an alias for each expression in the select-list. Using an alias allows you to reference the select-list expressions from elsewhere in the query, such as from within the WHERE and ORDER BY clauses. What version/build are you using? (03 Feb '17, 02:19) Volker Barth

You will have to use a SELECT in a SELECT:

SELECT FirstName, LastName, Other, 
    Case When Other is not null 
         then 1 
         else 0 END as Flag
FROM 
(
    select FirstName, LastName,
        Case
         When LastName = 'Jones'
         then 'N/A'
        End as Other
    from yourTable
) x

Or you can reuse the CASE statement for the Other field when evaluating the Flag field:

select FirstName, 
    LastName,
    Case 
        When LastName = 'Jones'
        then 'N/A'
    End as Other,
    case when LastName = 'Jones' -- your Other CASE code here
        then 1
        else 0
    END as flag 
from yourTable

SQL Alias | JOIN, The point is that you have an expression in the SELECT list that you assign with an alias, and you want to refer to that alias elsewhere in the  in the SELECT are done after all the other clauses are done. The "AS" operator can also give names to expressions in the SELECT list. These new names come into existence all at once, but after the WHERE clause, GROUP BY clause and HAVING clause has been executed; you cannot use them in the SELECT list or the WHERE clause for that reason.

Tip: APPLY and Reuse of Column Aliases, An alias can be used in a query select list to give a column a different name. Elsewhere in the statement, quoted references to the alias must use identifier  An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column: Press CTRL+C to copy. SELECT SQRT (a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0; SELECT id, COUNT (*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0; SELECT id AS 'Customer identity' FROM tbl_name;

MySQL 8.0 Reference Manual :: B.4.4.4 Problems with , This name may be used elsewhere in the select list and in other clauses to refer In the case of a column name in a select list, the alias name is optional Individual items in the select list, such as expressions and column references, are​  Using a table alias also makes it easier to use the autocomplete feature of most IDEs. In an IDE, to add a column into the SELECT statement, you’ll need to type out the full name of the column. Using a table alias, you can enter the table alias and then a period, and then a list of available columns will appear form that table.

SQL Anywhere Studio 9 Developer's Guide, At the Select objects: prompt, pick the vertical red line and right-click to end object selection. Close the Load/Unload Customizations dialog box. n Aliases: Aliases are used to reference the shortcut menu from elsewhere in the menu. An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column: SELECT SQRT (a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0; SELECT id, COUNT (*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0; SELECT id AS 'Customer identity' FROM tbl_name ;

Comments
  • Geez I hate this about MSSQL. Useless contribution, I am aware.
  • @youcantryreachingme this is actually just about every RDBMS that follows the standard...