Constrain a quey by Case conditions Results

[ipoppy28]

Situation:
'The ValidDate Case will convert all erroneous dates into functional dates.

Problem:
I need to be able to use a where condition (ValidDate > '01/01/1900'...)to get the results I need. When I attempt this I get

Error:
'The column prefix 'ValidDate' does not match with a table name or alias name used in the query.' Help Please



Code:
CREATE TABLE #LastModifiedOnHoldTable (EntityInstanceID int, DateModified datetime, ConvertedDateOpen datetime)

INSERT INTO #LastModifiedOnHoldTable
(EntityInstanceID, DateModified,ConvertedDateOPen)


SELECT Req_Table_1.EntityInstanceID, max(journal.timestamp) as DateModified,

ValidDate =
Case
WHEN isdate(Req_Table_3.fieldvalue) = 1
THEN Cast(Req_Table_3.fieldvalue as datetime)


WHEN isnumeric(substring(Req_Table_3.fieldvalue,1,1)) = 0
THEN Cast(Req_Table_3.fieldvalue as datetime)
ENd

FROM Data Req_Table_1
--eliminate the deleted reqs
JOIN EntityInstance
ON Req_Table_1.EntityInstanceID = EntityInstance.EntityInstanceID
AND EntityInstance.Deleted = 0
--Get only the reqs that were opened before the given date
JOIN Data Req_Table_3
ON Req_Table_1.EntityInstanceID = Req_Table_3.EntityInstanceID
AND Req_Table_3.FieldID = 185

WHERE Req_Table_1.FieldID = 187 -- requisition

 

[sunila7]

Hi,
Try this SQL
CREATE TABLE #LastModifiedOnHoldTable (EntityInstanceID int, DateModified datetime, ConvertedDateOpen datetime)

INSERT INTO #LastModifiedOnHoldTable
(EntityInstanceID, DateModified,ConvertedDateOPen)


SELECT Req_Table_1.EntityInstanceID, max(journal.timestamp) as DateModified,


Case Req_Table_3.fieldvalue
WHEN 1
THEN Cast(Req_Table_3.fieldvalue as datetime)


WHEN 0
THEN Cast(Req_Table_3.fieldvalue as datetime)
ENd ValidDate

FROM Data Req_Table_1
--eliminate the deleted reqs
JOIN EntityInstance
ON Req_Table_1.EntityInstanceID = EntityInstance.EntityInstanceID
AND EntityInstance.Deleted = 0
--Get only the reqs that were opened before the given date
JOIN Data Req_Table_3
ON Req_Table_1.EntityInstanceID = Req_Table_3.EntityInstanceID
AND Req_Table_3.FieldID = 185

WHERE Req_Table_1.FieldID = 187 -- requisition

I made some changes to Case Statment.

Sunil