473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

Understanding How T-SQL Works

NeoPa
32,556 Expert Mod 16PB
I have a problem understanding how/why T-SQL refuses the following line within a View :
Expand|Select|Wrap|Line Numbers
  1. IIF([dbo].[tblShift].[E_Status]='C',IIF([dbo].[Employees].[Pending],'P',IIF([dbo].[Employees].[Qualified],'Q','N')),[dbo].[tblShift].[E_Status]) AS [EmpType]
Error:
Expand|Select|Wrap|Line Numbers
  1. Error in list of function arguments: '=' not recognized.
  2. 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?
May 20 '15 #1
7 1951
Rabbit
12,516 Expert Mod 8TB
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
May 20 '15 #2
NeoPa
32,556 Expert Mod 16PB
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.
May 21 '15 #3
Rabbit
12,516 Expert Mod 8TB
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.
May 21 '15 #4
NeoPa
32,556 Expert Mod 16PB
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.
May 23 '15 #5
jforbes
1,107 Expert 1GB
Maybe you are having trouble with:
Expand|Select|Wrap|Line Numbers
  1. IIF([dbo].[tblShift].[E_Status]='C',IIF([dbo].[Employees].[Pending],'P',IIF([dbo].[Employees].[Qualified],'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.
May 24 '15 #6
NeoPa
32,556 Expert Mod 16PB
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.
May 24 '15 #7
NeoPa
32,556 Expert Mod 16PB
It was good thinking, but the error returned was exactly the same :-(

Certainly worth a try :-)
May 24 '15 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Steve | last post by:
Hi; I'm brand spanking new to sqlserver ( nice so far ). I need to make a simple data change across a list of tables. Basically replace an old date with a new date. However, the people I am...
2
by: Steve | last post by:
Hi; I have been writing a lot of short tsql scripts to fix a lot of tiny database issues. I was wondering if a could make an array of strings in tsql that I could process in a loop, something...
18
by: mountain man | last post by:
Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between...
2
by: dynoweb | last post by:
I have several *.sql files with schema/data changes to be applied to our current database. Is there a way to create a TSQL script that could be run from the SQL Query Analyzer that would...
18
by: war_wheelan | last post by:
I am very new to Transact-SQL programming and don't have a programming background and was hoping that someone could point me in the right direction. I have a SELECT statement SELECT FIXID,...
1
by: TOM GUGGER | last post by:
OMNI GROUP tgugger@aimexec.com T-SQL/ CONTRACT TO PERM/ ATLANTA
3
by: David Lozzi | last post by:
Howdy, ISSUE 1: See issue 2 below. I have a distance calculator on my site which works great. However, the users need to sort by distance, which make sense. I'm not sure how to do it other than...
16
by: David Lozzi | last post by:
Hello, I have some code that adds a new user. The new user has a checkboxlist of items which they can be associated with. I would like to send this list of items to TSQL along with the new user...
6
by: Jayson | last post by:
i've noticed some strange behavior in .net when it comes to figuring out the previous month via code. this only happens when the month is january, and i know why it's happening, i just don't...
8
by: David Lozzi | last post by:
I'm fairly new to ASP.Net 2.0 SQLDatasource objects. It defaults using TSQL statments for the SELECT, INSERT, UPDATE, DELETE commands, which is great and it works. However, I've always been taught...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.