Understanding How T-SQL Works

[NeoPa]

I have a problem understanding how/why T-SQL refuses the following line within a View :

IIF([dbo].[tblShift].[E_Status]='C',IIF([dbo].[Employees].[Pending],'P',IIF([dbo].[Employees].[Qualified],'Q','N')),[dbo].[tblShift].[E_Status]) AS [EmpType]

Error:

Error in list of function arguments: '=' not recognized.
Unable to parse query text.

It seems to be that, unlike when processing the same code in Access, it cannot, or will not, resolve expressions prior to passing the results into a function.

I'd be grateful for a better understanding of why this is so?

[Rabbit]

This is a query running in SQL Server? The IIf function doesn't exist in SQL Server, you have to use the CASE ... WHEN ... END statement instead.

https://msdn.microsoft.com/en-us/library/ms181765.aspx

[NeoPa]

I admit that was also my understanding, but then I found {MSDN} IIF (Transact-SQL).

They also have a {MSDN} CHOOSE (Transact-SQL) now too.

[Rabbit]

I stand corrected, I'm still on 2008. Unfortunately I don't know why it doesn't work in a query. Have you tried converting it to case? Case has better support across platforms.

[NeoPa]

Yes. And that certainly does work.

I've always found CASE to be such an unweildy language construct though. Reasonably powerful, but wordy to the max.

[jforbes]

Maybe you are having trouble with:

IIF([dbo].[tblShift].[E_Status]='C',IIF([icode][dbo].[Employees].[Pending][/icode],'P',IIF([icode][dbo].[Employees].[Qualified][/icode],'Q','N')),[dbo].[tblShift].[E_Status]) AS [EmpType]

It's possible that it's not being evaluated as a Boolean expression. Putting a "=True" or "=1" in there might get it working.

[NeoPa]

Good thinking J.

I must admit that I've recently learned that SQL doesn't like to treat values as booleans for some reason, but it was after I posted this and I didn't tie the two together until now.

I'll let you know how that works out.

[NeoPa]

It was good thinking, but the error returned was exactly the same :-(

Certainly worth a try :-)