October 29, 2015 at 2:47 pm
Access DB - can create an Alias with some formula.
Then, can take the Alias output and reference it
Tried this in the SQL Server editor. The line commented out just before the From statement won't recognize the Alias field above.
SELECT [ID_APD_Stips1]
,[Stip_Abv]
,(select [Stip_Name]+'WazaMatter') as STIP_WasaMatter
-- , (select [STIP_WasaMatter] & 'you') as Stip_WasaMatter_you -- This field is not recognized
FROM [RegDB].[dbo].[APD_Stips1]
GO
-- However the SQL editor doesn't recognize the Alias name [STIP_WasaMatter]
October 29, 2015 at 2:55 pm
SQL Server will not recognize the alias because it doesn't exist yet. It only exists once the value is selected from the table and since all the data is selected at one time, the alias can't be used in the select statement.
For example:
SELECT lname AS 'LastName',
FROM mytable
What that really says is "Get the data from the lname column in mytable and return it with a column name of LastName. So the alias doesn't get applied until the data is returned.
-SQLBill
October 29, 2015 at 4:18 pm
OK Then, it requires an entire Select Statement for the Table in order to use the output of the table to add the next level.
Thanks
SELECT [ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter +'You' as Stip_WasaMatter_you
FROM
(SELECT
[ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter = [Stip_Name]+'WazaMatter'
FROM [RegulatoryDB].[dbo].[APD_Stips1])
as FinalOutput
October 30, 2015 at 2:06 pm
Since the FROM clause is processed before the SELECT clause, you can use a CROSS APPLY to create a reusable alias.
SELECT [ID_APD_Stips1], [Stip_Abv], STIP_WasaMatter +'You' as Stip_WasaMatter_you
FROM [RegulatoryDB].[dbo].[APD_Stips1]
CROSS APPLY ( VALUES([Stip_Name]+'WazaMatter') ) calc(STIP_WasaMatter)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 30, 2015 at 2:38 pm
Just to clear something up. You absolutely can use an alias inside a SQL query but only after the alias has been created. It's important to clear that up.
SQL was written to be as close to English as possible which is why the SELECT statement comes first but the SELECT statement isn't processed first. This throws people. The major (keeping this simple for brevity): logical query processing phases are:
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
So although SELECT appears first in a query, it's actually processed after FROM, WHERE, GROUP BY, etc... This is why you can't use an alias in your joins, WHERE clause, etc.. because SQL Server has not processed your SELECT statement and therefore the alias has not been created. You can, however, use an alias in the ORDER BY clause because that is processed after SELECT.
Here's a full chart by Itzik Ben-Gan: Logical Query Processing
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply