CASE Statements vs IIF Statements

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 2005 Forums

Transact-SQL (2005)

CASE Statements vs IIF Statements

Author

Topic

GreyPilgrim
Starting Member

2 Posts

Posted - 2007-01-18 : 11:19:58

I'm trying to convert an Access SQL Statement to run in SQL Server 2000. The Access version is an aggregate query containing two MAX(IIF...) statements - I'm trying to rewrite them as MAX(CASE..) statements but I'm getting an error so clearly screwing up somewhere. New to this...I'm really trying...

The tricky lines are in lines 6 and 7 of the two code samples below. any help is GREATLY appreciated.

ACCESS STATEMENT
****************

SELECT
tblGroupTypes.GroupTypeName,
tblGroups.GroupName,
tblGroups.Description,
tblGroups.GroupID,
Max(IIf([tblGroups].[OwnerID]=1,"1","0")) AS Owned,
Max(IIf([tblGroupMembership].[UserID]=1,"1","0")) AS Member

FROM
(tblGroupTypes INNER JOIN tblGroups ON tblGroupTypes.GroupTypeID = tblGroups.GroupType)
INNER JOIN tblGroupMembership ON tblGroups.GroupID = tblGroupMembership.GroupID

GROUP BY
tblGroupTypes.GroupTypeName,
tblGroups.GroupName,
tblGroups.Description,
tblGroups.GroupID

ORDER BY
tblGroups.GroupID;

SQL STATEMENT
*************

SELECT
tblGroupTypes.GroupTypeName,
tblGroups.GroupName,
tblGroups.Description,
tblGroups.GroupID,
MAX(CASE tblgroups.OwnerID WHEN 1 THEN 1 ELSE 0) AS OWNED
MAX(CASE tblGroupMembership.UserID WHEN 1 THEN 1 ELSE 0) AS MEMBER

FROM
(tblGroupTypes INNER JOIN tblGroups ON tblGroupTypes.GroupTypeID = tblGroups.GroupType)
INNER JOIN tblGroupMembership ON tblGroups.GroupID = tblGroupMembership.GroupID

GROUP BY
tblGroupTypes.GroupTypeName,
tblGroups.GroupName,
tblGroups.Description,
tblGroups.GroupID

ORDER BY
tblGroups.GroupID

GreyPilgrim
Starting Member

2 Posts

Posted - 2007-01-18 : 11:26:08

IGNORE!!!!!!!!!!!!

I realised I missed the END from the Case statement block...

...but I'd made an even more stupid mistake which I'm too embarrassed to mention.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources