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?
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