SQL Server 2000 Null Conversion

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Server 2000 Null Conversion

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-18 : 08:22:46

Matt writes "I have this query in Access that I am desperately trying to import into MS SQL Server 2000. I have been pulling my hair out trying to get it to work. The query is:

SELECT qryGeneralExpandAllocsAndBlockItems.ID AS AllocationID, qryGeneralExpandAllocsAndBlockItems.DayID AS ColID, qryGeneralExpandAllocsAndBlockItems.PeriodID AS RowID, tblSubjects.Code AS ItemCode1, tblSubjects.Name AS ItemName1, IIf([qryGeneralExpandAllocsAndBlockItems].[BlockID] Is Null,[tblForm].[Code],[tblBlock].[Name]) AS ItemCode2, IIf([qryGeneralExpandAllocsAndBlockItems].[BlockID] Is Null,[tblForm].[Name],[tblBlock].[Name]) AS ItemName2, tblRoom.Code AS ItemCode3, tblRoom.Name AS ItemName3, Not IsNull([qryGeneralExpandAllocsAndBlockItems].[BlockID]) AS IsBlock, qryGeneralExpandAllocsAndBlockItems.BlockItemID, qryGeneralExpandAllocsAndBlockItems.PeriodID
FROM (((qryGeneralExpandAllocsAndBlockItems LEFT JOIN tblForm ON qryGeneralExpandAllocsAndBlockItems.FormID = tblForm.ID) LEFT JOIN tblRoom ON qryGeneralExpandAllocsAndBlockItems.RoomID = tblRoom.ID) LEFT JOIN tblBlock ON qryGeneralExpandAllocsAndBlockItems.BlockID = tblBlock.ID) LEFT JOIN tblSubjects ON qryGeneralExpandAllocsAndBlockItems.SubjectID = tblSubjects.ID
WHERE (((qryGeneralExpandAllocsAndBlockItems.DayID) Is Not Null) AND ((qryGeneralExpandAllocsAndBlockItems.PeriodID) Is Not Null) AND ((qryGeneralExpandAllocsAndBlockItems.TeacherID)=[pFilterID]))
ORDER BY qryGeneralExpandAllocsAndBlockItems.ID;

I believe it is an error to do with the way SQL server treats NULLs? Any help would be greatfully apreciated. It works fine as an Access query."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-18 : 08:31:57

there's no IIF in T-SQL. use

case when condition1 then result1
when condition2 then result2
...
else defaultResult
end

Go with the flow & have fun! Else fight the flow

B0g
Starting Member

19 Posts

Posted - 2004-11-19 : 03:54:13

CASE works fine, but it's more simple using ISNULL or COALESCE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources