Conditional formatting if then statements

Question

I am trying to get what would be considered a 'nested if' function in excel to formulate in a query. The goal of the query is to create a view so that I can compare two sets of data.

If in excel the formula would look like this: =IF(condition1=0,condition2,IF(condition2=0,condition3,condition1)).

I keep getting this error message:

The query language I have tried to use is:

drop view danburycomp 
go
create view danburycomp as
SELECT *, 
       TotalCash=CASE 
                   WHEN [cash out] = 0             THEN [cash counter cash in] 
                   WHEN [cash counter cash in] = 0 THEN [cash counter cash out] 
                                                   ELSE [cash out] 
                 END 
FROM   [trans jan-mar2016] 
WHERE  [account number] IN ( 'UNIQUEID1', 'UNIQUEID2' ) 
  AND ( [cash into trans] != 0 
         OR [cash out] != 0 ) 
  AND [date time trans] BETWEEN '2016-01-29' AND '2016-02-24' 

Answer 1

Two things:

1. Instead of TotalCash= put as TotalCash after the end in the case statement.

2. The case logic is off. If Cash out <> 0 and [Cash Counter Cash In]=0 it will return [Cash Counter Cash Out] which I believe is not what is wanted.

so use this:

 drop view danburycomp 
go
create view danburycomp as
select *,
case 
    when[Cash Out]=0 and [Cash Counter Cash In]<>0 then [Cash Counter Cash In] 
    when [Cash Out]=0 and[Cash Counter Cash In]=0 then [Cash Counter Cash Out] 
    else [Cash Out]
end as TotalCash
from [Trans Jan-Mar2016]
where [Account Number] in ('UNIQUEID1', 'UNIQUEID2')
and ([Cash Into Trans] !=0 or [Cash Out]!=0)
and [Date Time Trans] between '2016-01-29' and '2016-02-24'