computed column in WHERE clause

Last Post 04 Nov 2008 09:55 AM by ConKi. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
ConKi
New Member
New Member

--
28 Oct 2008 10:10 AM
Good morning all,

I have a computed column in the SELECT statement and now I want to filter the result set using the computed column in the WHERE clause.
I understand that the WHERE clause is run before the SELECT clause, so there is no way I can get the computed column in the WHERE clause, but I think there should be a work around out there.
I tried table variable, temp table, WITH, but all do not work.

Please help,

Thanks,

ConKi
ConKi
New Member
New Member

--
28 Oct 2008 10:31 AM
I got it. I just put the expression in the where clause, but any other solutions is appreciated.
nosepicker
New Member
New Member

--
28 Oct 2008 10:36 AM
Post your SQL statement and we'll see.
ConKi
New Member
New Member

--
28 Oct 2008 10:53 AM
Good morning,
Here is my solution.

-- Does not work:
SELECT RowId AS ObjectId
,SUBSTRING(DateCreated, CHARINDEX(':', DateCreated) + 1, CHARINDEX('-', DateCreated) - CHARINDEX(':', DateCreated) - 1) AS DateCreated
FROM Description
WHERE DateCreated = '06'
ORDER BY MonthCreated

-- Worked:
SELECT RowId AS ObjectId
,SUBSTRING(DateCreated, CHARINDEX(':', DateCreated) + 1, CHARINDEX('-', DateCreated) - CHARINDEX(':', DateCreated) - 1) AS DateCreated
FROM Description
WHERE LTRIM(LEFT(SUBSTRING(DateCreated, CHARINDEX(':', DateCreated) + 1, CHARINDEX('-', DateCreated) - CHARINDEX(':', DateCreated) - 1),3)) = '06'
ORDER BY MonthCreated

Thanks,

ConKi
nosepicker
New Member
New Member

--
28 Oct 2008 03:12 PM
Yes, you can refer to column aliases in the ORDER BY clause, but not the WHERE clause or GROUP BY clause. So the way you did it is correct. BTW, what do your DateCreated values look like? Maybe there's an easier way to extract your dates.

ConKi
New Member
New Member

--
04 Nov 2008 09:55 AM
Hi,
The DateCreated is in the form of MM/DD/YYYY. I get it from the header of the stored procedure script.

For ex:

-- Author: BLA BLA
-- Create date: 01/10/2008
-- Description: This stored procedure select information from table

ConKi
You are not authorized to post a reply.

Acceptable Use Policy