Select inside CASE THEN

I need to select the project rate or shift rate that has the effective date less than today.

 SELECT CASE WHEN ISNULL(s.rate,0) = 0 THEN SELECT TOP 1 pr.rate FROM ProjectRates pr WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC --p.rate ELSE SELECT TOP 1 sr.rate FROM ShiftRates sr WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC --s.rate END AS rate FROM Projects p INNER JOIN Shifts s ON (p.ID = s.projectID) WHERE (p.ID = @projectID) 

Please note that this code snippet is part of a larger stored proc and thus it must be within a CASE statement.

1 Answer

Subqueries need parentheses:

SELECT (CASE WHEN ISNULL(s.rate, 0) = 0 THEN (SELECT TOP 1 pr.rate FROM ProjectRates pr WHERE (pr.projectID = p.ID) AND (pr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC ) ELSE (SELECT TOP 1 sr.rate FROM ShiftRates sr WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate < GETDATE()) ORDER BY pr.effectiveDate DESC ) --s.rate END) AS rate FROM Projects p INNER JOIN Shifts s ON p.ID = s.projectID WHERE p.ID = @projectID;