isnull function requires 2 args

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

isnull function requires 2 args

Author

Topic

alluvignesh
Starting Member

5 Posts

Posted - 2011-03-29 : 14:05:57

SELECT TblEmployee.LastName AS [Last], TblEmployee.[Known As] AS [First], [TelephoneNumber]+" "+IIf(IsNull([Extension])," ","x"+[Extension]) AS Phone, IIf(IsNull([tblEmployee].[UserName]),"n/a","<a href=""mailto:"+[tblEmployee].[UserName]+"@abc.com"">"+[tblEmployee].[UserName]+"@abc.com"+"</a>") AS eMail, "<a href=""teams.asp#"+[TeamName]+""">"+[TeamName]+"</a>" AS Team
FROM TblTeam INNER JOIN ((TblEmployee INNER JOIN TblEmployee AS TblEmployee_1 ON TblEmployee.SupervisorID = TblEmployee_1.PID) INNER JOIN tblAssignedLocations ON TblEmployee.PID = tblAssignedLocations.PID) ON TblTeam.TeamId = tblAssignedLocations.TeamID
WHERE ((TblEmployee.Inactive)=False)
ORDER BY TblEmployee.LastName, TblEmployee.[Known As], "<a href=""teams.asp#"+[TeamName]+""">"+[TeamName]+"</a>", TblEmployee.TeamLeader, TblEmployee.Supervisor;What i', doing wrong here? I'm getting "The isnull function requires 2 argument(s)" error while executing the query. Any help will be greatly appreciated. Thanks!

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:09:33

ISNULL requires 2 values, the one you are analyzing (such as a column name) and the one you want to replace it with if it is NULL.

Some programs use ISNULL as a conditional check that returns a yes/no, but not SQL.

Hey, it compiles.

alluvignesh
Starting Member

5 Posts

Posted - 2011-03-29 : 14:10:09

Sorry used the wrong font...

SELECT TblEmployee.LastName AS [Last], TblEmployee.[Known As] AS [First], [TelephoneNumber]+" "+IIf(IsNull([Extension])," ","x"+[Extension]) AS Phone, IIf(IsNull([tblEmployee].[UserName]),"n/a","<a href=""mailto:"+[tblEmployee].[UserName]+"@abc.com"">"+[tblEmployee].[AISUserName]+"@abc.com"+"</a>") AS eMail, "<a href=""teams.asp#"+[TeamName]+""">"+[TeamName]+"</a>" AS Team
FROM TblTeam INNER JOIN ((TblEmployee INNER JOIN TblEmployee AS TblEmployee_1 ON TblEmployee.SupervisorID = TblEmployee_1.PID) INNER JOIN tblAssignedLocations ON TblEmployee.PID = tblAssignedLocations.PID) ON TblTeam.TeamId = tblAssignedLocations.TeamID
WHERE ((TblEmployee.Inactive)=False)
ORDER BY TblEmployee.LastName, TblEmployee.[Known As], "<a href=""teams.asp#"+[TeamName]+""">"+[TeamName]+"</a>", TblEmployee.TeamLeader, TblEmployee.Supervisor;

What i', doing wrong here? I'm getting "The isnull function requires 2 argument(s)" error while executing the query. Any help will be greatly appreciated. Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-29 : 14:18:29

What "SQL" are you using?

IIF is not a SQL Server funtion.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:20:40

Is this for Access or SQL server?

Hey, it compiles.

alluvignesh
Starting Member

5 Posts

Posted - 2011-03-29 : 14:24:56

I'm trying to execute this query in SQL server. I'm not a SQL guy, I'm trying to use access query in SQL server.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:26:45

Access queries don't pipe in to SQL server too well. Here is a first stab at it for you:

SELECT
TblEmployee.LastName AS [Last],
TblEmployee.[Known As] AS [First],
[TelephoneNumber]+' '+
CASE WHEN([Extension] IS NULL)THEN
' '
ELSE
'x'+[Extension]
END AS Phone,
CASE WHEN([tblEmployee].[AISUserName] IS NULL)THEN
'n/a',
ELSE
'<a href="mailto:' + [tblEmployee].[AISUserName]+ '@ais.msu.edu">' + [tblEmployee].[AISUserName] + '@ais.msu.edu'+'</a>'
END AS eMail,
'<a href="teams.asp#'+[TeamName]+'">'+[TeamName]+'</a>' AS Team
FROM TblTeam
INNER JOIN ((TblEmployee INNER JOIN TblEmployee AS TblEmployee_1 ON TblEmployee.SupervisorID = TblEmployee_1.PID)
INNER JOIN tblAssignedLocations ON TblEmployee.PID = tblAssignedLocations.PID) ON TblTeam.TeamId = tblAssignedLocations.TeamID
WHERE ((TblEmployee.Inactive)=False)
ORDER BY TblEmployee.LastName, TblEmployee.[Known As], '<a href="'teams.asp#'+[TeamName]+'">'+[TeamName]+'</a>', TblEmployee.TeamLeader, TblEmployee.Supervisor;

Hey, it compiles.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:27:52

The CASE WHEN statement usually works nicely in place of IIF. Also, double quotes " are replaced by single quotes.

Hey, it compiles.

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:32:27

The WHERE clause...tell me about the column "Inactive". Is it a 0/1 column? Is it text ("False"/"True")? I think that there will be a problem with it as it is.

Hey, it compiles.

alluvignesh
Starting Member

5 Posts

Posted - 2011-03-29 : 14:35:18

quote:
Originally posted by ajthepoolman

The WHERE clause...tell me about the column "Inactive". Is it a 0/1 column? Is it text ("False"/"True")? I think that there will be a problem with it as it is.

Hey, it compiles.

Awsome, It works like a charm! Thanks, ajthepoolman!

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-03-29 : 14:47:24

You are very welcome!

Hey, it compiles.

alluvignesh
Starting Member

5 Posts

Posted - 2011-03-29 : 15:18:19

quote:
Originally posted by ajthepoolman

The WHERE clause...tell me about the column "Inactive". Is it a 0/1 column? Is it text ("False"/"True")? I think that there will be a problem with it as it is.

Hey, it compiles.

It's 0/1. I fixed it. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources