Trying to return one value in IIF to achieve a Distinct (max value in status hierarchy)

[kkishba2]

The case statement below is producing the intended hierarchy, but I need to return only ONE of the values for a distinct status in my overall SQL statement (which is why I was trying to perform a nested IIF).

Case when se.STATUS in ('Final Reviewed','Reported') then 'Final Result'
when se.STATUS in ('Bench Reviewed', 'Peer Reviewed') then 'Preliminary/Discretionary Result'
when se.STATUS in ('Available', 'Batched','[Group]', 'Received', 'SubContracted') then 'Received/No Results'
when se.STATUS = 'Disapproved' then 'Disapproved'
else se.STATUS END AS ResultStatus,

IIF (ResultStatus ='Final Result', 'Final Result',
IIF(ResultStatus='Preliminary/Discretionary Result', 'Preliminary/Discretionary Result',
IIF(ResultStatus='Received/No Results', 'Received/No Results',
IIF(ResultStatus='Disapproved', 'Disapproved'))
, ResultStatus) as Status

I receive the following error message:

Msg 102, Level 15, State 1, Line 62
Incorrect syntax near ')'.

I am quite sure I have either too many or not enough parens to achieve a distinct value for "Status".

Can someone assist me - or point me in the right direction?

[divyed · Accepted solution]

Hello @kkishba2 ,

I think there is small syntex error here. Can you try below code please :

IIF (ResultStatus = 'Final Result', 'Final Result',
IIF(ResultStatus = 'Preliminary/Discretionary Result', 'Preliminary/Discretionary Result',
IIF(ResultStatus = 'Received/No Results', 'Received/No Results',
IIF(ResultStatus = 'Disapproved', 'Disapproved', ResultStatus))))
AS Status

I hope this will solve your issue , If yes, please mark this as solution

Cheers

[kkishba2]

I am attempting write a script to create a view in SSMS that I can include in my semantic model for reporting in Power BI.  Have I posted to the wrong forum?

[lbendlin]

yes, you have.

Anyway

but I need to return only ONE of the values for a distinct status in my overall SQL statement 

That's what CASE WHEN does - it exits after the first match, same like nested Ifs, or like SWITCH in Power BI.

Did you see an actual issue with the SQL code using CASE WHEN ?

[kkishba2]

My case statement is working as expected.  My IIF function is not working.

[lbendlin]

Where is the Power BI angle to your question?

[kkishba2]

Microsoft SSMS

I didn't include the entire SQL script - I am trying to narrow the return to only provide one "Status" statement, not each status throughout the lifecycle of an analyte test.

[lbendlin]

Which application/platform is this for?