April 27, 2011 at 1:12 pm
Hi, I found this online:
DECLARE @strValues varchar(8000)
SELECT @strValues = COALESCE(@strValues+',', '') + NodeName
FROM (SELECT DISTINCT NodeName FROM sys.dm_os_cluster_nodes) X
ORDER BY NodeName
SELECT @strValues as Result
The X seems to be an alias for a join but I always think of joins as join two things on a row so this method is a new one for me. Can anyone explain?
Thanks much,
Howard
April 27, 2011 at 1:15 pm
This is simply giving an alias to a subquery so it can be referenced by the outer query. Try running it without the "X" and see what you get. Sometimes aliasing is required and it is always good practice in case you want to reference it more explicitly later.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 1:18 pm
It does not work without the X. I don't understand in what part of the query execution it is referencing the subquery by alias.
April 27, 2011 at 1:24 pm
PHXHoward (4/27/2011)
It does not work without the X. I don't understand in what part of the query execution it is referencing the subquery by alias.
It is the entire outer query. in 2008 if you try to use a subquery without aliasing it, you'll get an error. Even this code will fail
SELECT FirstThing, SecondThing, ThirdThing
FROM (SELECT FirstThing, SecondThing, ThirdThing FROM MyTable)
Unless you add an alias at the end of the subquery.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 27, 2011 at 1:25 pm
It is expected when the FROM clause is fed by a query in brackets that an alias is given.
Even though the X is not explicitly referenced by the rest of the query if you prefix all columns with X and run the query it will still work as X is the local alias.
Without it, as you found, an error will appear.
HTH.
---------------------------------------------
If data can screw you, it will; never assume the data are correct.
April 27, 2011 at 1:27 pm
Thanks Stefan. I see what you are saying and will experiment with it.
Howard
April 27, 2011 at 1:28 pm
Thanks Kevin. Makes sense now.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply