Menu
I wounder how I could use an alias in a where statement.
Example :
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 FROM MyTable WHERE Col1 = 'MySearch' I use MSSQL 2005
941
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
No you can't reference the column alias in the WHERE clause.
Alias can be performed using the 'AS' keyword or without any keyword. But it is recommended to use the 'AS' keyword to avoid confusion between the initial column name and column alias. If alias name contain space or any special character, enclose alias name in single(' ') or double(” “) quotes.
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition];
Alias for Tables Example. The following SQL statement selects all the orders from the customer with CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and give them the table aliases of "c" and "o" respectively (Here we use aliases to make the SQL shorter): SELECT o.OrderID, o.OrderDate, c.CustomerName.
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. In MySQL you can at least reuse aliases in the SELECT clause
To make the query shorter, you use the table aliases, for example, e for employees table and d for departments table as the following query: SELECT employee_id, first_name, last_name, e.department_id, department_name FROM employees e INNER JOIN departments d ON d.department_id = e.department_id ORDER BY first_name;
Actually, using alias won't make your query any faster as SQL optimizer is not as dumb as you think, so I'd just repeat the SUBSTRING expression again. Show activity on this post. With PostgreSQL 9.3+ OR Oracle 12c, there is now lateral join that allows creating an alias. Lateral joins are joints inside witch you can reference preceding tables.
You can use "having" instead of "where".
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1 FROM MyTable HAVING Col1 = 'MySearch' Having do a "where" after execution of the query. Be careful to use it in the right conditions to have no performance problem.
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
