IF clause in SQL from statement

Question

I need a workaround for the problem I created. Essentially, I want to calculate two values, but use a different method, depending on the result of the condition.

select userReturnval, userregisterid, OtherValue FROM ( (SELECT otherValue FROM... ) as tblO --unrelated table , ( if (select count(userregisterid) from table1 where site =@siteID and userid=@userID ) >0 SELECT userReturnval, userregisterid FROM ( SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY ...) as RANK FROM ... WHERE --first where clause ) as tblRank WHERE (RANK =1) else SELECT userReturnval, userregisterid FROM ( SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY ...) as RANK FROM ... WHERE --different where clause ) as tblRank WHERE (RANK =1) ) as tblR 

My if works fine on its own, I just want it to work as part of a larger request. At the moment, sqlserver does not like it if it is there.

Hope someone can point me in the right direction!

Answer 1

You can try using case statements in the WHERE clause, something like the instructions below. Please note: I do not think that this will be especially optimal for performance.

Performing this action allows you to save it in a single expression:

 select userReturnval, userregisterid, OtherValue FROM ( (SELECT otherValue FROM... ) as tblO --unrelated table , ( SELECT userReturnval, userregisterid FROM ( SELECT userReturnval, userregisterid, Rank() OVER (PARTITION BY .. ORDER BY ...) as RANK FROM ... WHERE case --Choose which where clause to use when (select count(userregisterid) from table1 where site =@siteID and userid=@userID ) >0 then case when /*First where clause*/ then 1 else 0 end else case when /*Second where clause*/ then 1 else 0 end end = 1 ) as tblRank WHERE (RANK =1) ) as tblR