May 13, 2009 at 6:14 am
Hi,
Can I refer to a new column name in a SELECT statement? Consider this:
SELECT (firstname + ' ' + LastName) AS FullName
WHERE FullName like '%Dalton%'
Can I refer in the WHERE clause to this aliased name FullName? I can't do it, the above returns an error, which means I have to use the concatenation again. I realise I could put the resulkt in a table variable or such and then do the WHERE select, but that would mean an extra statement. I assume the error is because the alias is made in the same statement where I make the selection on this alias.
If there is any other way to accomplish this I would appreciate some input.
Greetz,
Hans Brouwer
May 13, 2009 at 6:18 am
won't work like that
you can use
select * from
(
SELECT (firstname + ' ' + LastName) AS FullName) as derivedtable
WHERE derivedtable.FullName like '%Dalton%'
This works by returning a data set with the new column,which you then perform a select statement on
For more comlicated queries a CTE (common table expression) will be a better option
May 13, 2009 at 6:29 am
OK, tnx for answering; this would work, but it looks mightily expensive. I know I can use a CTE, but I fear that would also be mightily expensive.
I'll check it out anyway, tnx for answering.
Greetz,
Hans Brouwer
May 13, 2009 at 6:35 am
Run a few tests on it 🙂
Use this code from RBarryYoung as a test wrapper:
Set NoCount On
Declare @cpu_ int
Declare @lreads_ int
Declare @eMsec_ int
Select @cpu_ = cpu_time
, @lreads_ = logical_reads
, @eMsec_ = total_elapsed_time
From sys.dm_exec_requests
Where session_id = @@spid
--======
-- Your Test Code Goes Here
--======
Select cpu_time-@cpu_ as CpuMs
, logical_reads- @lreads_ as LogRds
, total_elapsed_time - @eMsec_ as Elapsed
From sys.dm_exec_requests
Where session_id = @@spid
May 13, 2009 at 6:42 am
Out of curiosity I just checked it on a 30 million row table
Result 1 - without a derived table
CpuMsLogRdsElapsed
80159430078641
Result 2 - with a derived table
CpuMsLogRdsElapsed
7766946009777
May 13, 2009 at 6:46 am
Interesting...
Maybe I should take a serious look at your solution.
Tnx again.
Greetz,
Hans Brouwer
May 13, 2009 at 6:54 am
FreeHansje (5/13/2009)
Interesting...Maybe I should take a serious look at your solution.
Tnx again.
For your future reference:
You never know untill you try! 😉
I had a situation a couple of weeks ago where a sub query was much more efficient than a join, went against what I thought was the perceived wisdom
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply