Hi everyone this is my first time in a Forum like this. I'm from Germany so English is not my native language so don't be too harsh with me please :).
Select top(1) firstname+' '+lastname AS APName
From ansprech
Where customernr = 10205 and APName LIKE '%Max Example';
I want to use the Alias I created in this Like statement. I've searched for almost an hour but I can't find the right answer to my problem so maybe you guys could help me :).
You can use subquery
SELECT * FROM
(Select firstname+' '+lastname AS APName From ansprech Where customernr = 10205 ) TMP WHERE APName LIKE '%Max Example';
OR
Select top(1) firstname+' '+lastname AS APName From ansprech Where (customernr = 10205) and ( firstname+' '+lastname LIKE '%Max Example');
On SQL Server (and Access), you don't have much choice here other than to use a subquery or to just repeat the full alias expression. I would go for the latter in this case:
SELECT TOP(1)
firstname + ' ' + lastname AS APName
FROM ansprech
WHERE customernr = 10205 AND firstname + ' ' + lastname LIKE '%Max Example';
SQL Server follows the below processing order, when you run a SELECT query.Read more about Processing Order of SELECT query
Logical Processing Order of the SELECT statement
The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause. The actual physical execution of the statement is determined by the query processor and the order may vary from this list.
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
Your Alias is coming in the SELECT stage 8 and so, it cannot be referred in the preceding stage 4(WHERE clause). If you want to reference alias in your WHERE clause, the alias should be part of the FROM clause(Stage 1), which is preceding WHERE clause(stage 4). What you can do is, have a subquery in the FROM clause to get alias as part of the result and use it subsequently in the WHERE clause.
SELECT top(1) APName
FROM (SELECT firstname+' '+lastname AS APName, CustomerNr From ansprech) AS c
WHERE customernr = 10205 and APName LIKE '%Max Example';
I prefer to use APPLY to simulate a variable-like behaviour:
USE master;
SELECT NewAlias
FROM [sys].[objects] o
CROSS APPLY(SELECT CONCAT(o.[name],' (',o.[object_id],')') AS NewAlias) A --A ist just an alias for the APPLY's result
WHERE NewAlias LIKE '%cols%';
The APPLY is a row wise call. If the engine can be sure, that this will return just one single row, this performs lightning fast. You can think of it as an additional computed column.
Like Shnugo, I am a big fan of defining identifiers in the FROM clause. For your query, this looks like:
select top(1) v.APName
from ansprech a cross apply
(values (a.firstname + ' ' + a.lastname)) v(APName)
where a.customernr = 10205 and
v.APName like '%Max Example';
