I am trying to create a column called DateStartedStatus that utilizes a previously aliased column to compute its value. It should use CurrentStatus to output a value and an error is showing that says “Invalid column name ‘CurrentStatus'”. How can I access that alias in the below case statement?
x
SELECT p.[ID]
,p.[Name] as 'ProcurementName'
,p.[FundingDocumentNumber] as 'FundingDocumentNumber'
,p.[Status]
,p.[Comments] as 'Comments'
,p.[isSAVE]
,p.[InWorkDate]
,p.[RoutedDate]
,p.[FundsCertifiedDate]
,p.[AwardedDate]
,p.[TransactionType]
,p.[FNMSStatus]
,p.[Closed]
,p.[Archived]
,p.[Cancelled]
,(CASE
WHEN p.[Status] = 'In Work' THEN 'Pending'
ELSE p.[Status]
END) as CurrentStatus
,(CASE
WHEN CurrentStatus = 'Awarded' THEN p.AwardedDate <-- fails here CurrentStatus not a column
END) as DateStartedStatus
,(SELECT SUM(TotalCost)
FROM ProcurementsRequestLineItems subprlis
LEFT JOIN RequestLineItems subrli ON subprlis.RequestLineItemID = subrli.ID
WHERE ProcurementID = p.ID) as TotalCost
FROM Procurements p
WHERE p.Closed = 0 AND p.Archived = 0;
Advertisement
Answer
Using subqueries like this
select CASE WHEN CurrentStatus .
from
( --calculate Current_status here
select .
CASE
WHEN p.[Status] = 'In Work' THEN 'Pending'
ELSE p.[Status]
END) as CurrentStatus
) s
Do not worry, subquery will not add computational complexity, optimizer will remove it if possible.
Another way is nested CASE expressions (query is not readable):
case when case some logic here end = 'Awarded'
then
end