Reference an alias elsewhere in the SELECT list
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 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
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 ;
- Geez I hate this about MSSQL. Useless contribution, I am aware.
- @youcantryreachingme this is actually just about every RDBMS that follows the standard...