| Author |
 Topic  |
|
|
vree
Starting Member
USA
30 Posts |
 Posted - 08/14/2006 : 14:42:23
|
I have an IIF statment in a query in Access;
SmallY: IIf([Small]=True,"YES"," ")
How can i translate this to SQL Server? |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 08/14/2006 : 14:45:47
|
IIF(BooleanExpression, TruePart, FalsePart)
translates to
CASE WHEN BooleanExpression THEN TruePart ELSE FalsePart END
in SQL Server. (More typing but also more flexible).
- Jeff |
Edited by - jsmith8858 on 08/14/2006 14:46:12 |
 |
|
|
vree
Starting Member
USA
30 Posts |
Posted - 08/14/2006 : 16:16:56
|
Thanks - I actually came up with this but would still like to Group it by Last Name.
SELECT dbo.tblContact.ContactId, dbo.tblContact.FirstName + ' ' + dbo.tblContact.LastName AS COMMITTEENAME,
dbo.tblCommittee.Committee,
dbo.tblMemberCompany.CompanyName, dbo.tblMemberCompany.MemberId,
dbo.tblContact.Address1, dbo.tblContact.City,
dbo.tblContact.State, dbo.tblContact.Zip, dbo.tblContact.Phone,
dbo.tblContact.Fax, dbo.tblContact.Email,
CASE Committee WHEN 'Industry Operations Committee' THEN 'Yes'
WHEN 'Network Subcommittee' THEN 'Yes'
WHEN 'Governmental Affairs Committee' THEN 'Yes'
WHEN 'Accounting & Finance Subcommittee' THEN 'Yes'
WHEN 'Associate Members Committee' THEN 'Yes'
WHEN 'Regulatory Subcommittee' THEN 'Yes'
WHEN 'Broadband Services Coalition' THEN 'Yes'
WHEN 'CLEC Coalition' THEN 'Yes'
WHEN 'Conferences Committee' THEN 'Yes'
WHEN 'Customer Service Subcommittee' THEN 'Yes'
WHEN 'Human Resources Subcommittee' THEN 'Yes'
WHEN 'Marketing Subcommittee' THEN 'Yes'
WHEN 'Membership & Bylaws Committee' THEN 'Yes'
WHEN 'Network Subcommittee' THEN 'Yes'
WHEN 'Marketing Subcommittee' THEN 'Yes'
Else ''
END As Committee
FROM dbo.tblCommitteeMember INNER JOIN
dbo.tblMemberContact ON
dbo.tblCommitteeMember.MemberContID = dbo.tblMemberContact.MemberContId
INNER JOIN
dbo.tblContact ON
dbo.tblMemberContact.ContactId = dbo.tblContact.ContactID INNER
JOIN
dbo.tblCommittee ON
dbo.tblCommitteeMember.CommitteeID = dbo.tblCommittee.CommitteeID
INNER JOIN
dbo.tblMemberCompany ON
dbo.tblMemberContact.MemberId = dbo.tblMemberCompany.MemberID
ORDER BY dbo.tblMemberCompany.CompanyName, COMMITTEENAME |
|
|
|
madhivanan
Premature Yak Congratulator
India
22469 Posts |
Posted - 08/15/2006 : 09:30:10
|
You can also simplify CASE as
Case WHEN Committee in('Industry Operations Committee','Network Subcommittee',...) THEN 'Yes' else '' END
Madhivanan
Failing to plan is Planning to fail |
|
|
|
vree
Starting Member
USA
30 Posts |
Posted - 08/15/2006 : 10:05:30
|
| Cool! Thank you will do! |
|
|
|
Spolarium7
Starting Member
Philippines
2 Posts |
Posted - 02/26/2008 : 02:37:15
|
Hey,
I have a similar question, I have a table with ID, FirstName, MiddleName, and LastName fields. Now, when I query this, I need only two fields : the ID and a FullName field formatted like so (LastName + [space] + [comma] + [space] + FirstName + [the middle initial if any (consisting of [space] + middleinitial + [period])
Examples would be:
Fugita,Tsuyoshi N.
Beleren, Jace
NOTE that in the first example, Tsuyoshi's middle name is 'Nakajima' so his middle initial is 'N.'. Also in second example, Jace has no middle name so, it should be blank.
HERE'S THE PROBLEM:
I have devised this statement which reads:
(CASE WHEN LEN(MiddleName) = 0 THEN LastName + ', ' + FirstName ELSE LastName + ', ' + FirstName + ' ' + LEFT(MiddleName, 1) + '.' END) AS FullName
But when I run this, it gives me the proper result for the first record but leaves the second record blank.
Can someone explain this?
NO rules. |
Edited by - Spolarium7 on 02/26/2008 02:49:01 |
|
|
|
Spolarium7
Starting Member
Philippines
2 Posts |
Posted - 02/26/2008 : 02:43:15
|
Wait, i think i've solved it:
CASE WHEN LEN(fldMN) > 0 THEN fldLN + ', ' + fldFN + ' ' + LEFT(fldMN, 1) + '.' ELSE fldLN + ', ' + fldFN END AS FullName
dont know why this one works and the other doesn't though... so I still beg for an explanation.
NO rules. |
Edited by - Spolarium7 on 02/26/2008 02:49:31 |
|
|
|
A028365
Starting Member
1 Posts |
Posted - 12/04/2012 : 11:30:02
|
I have the following statement in Access:
IIf([SVSX04]='S','HS',IIf([SVSX04]='A','AGE',IIf([SVSX04]='P' And ([SCSPGM]<>'341' And [SCSPGM]<>'371' And [SCSPGM]<>'372'),'PSAV',IIf([SVSX04]='P' And [SCSPGM]='341','CWE',IIf([SVSX04]='P' And [SCSPGM]='371','APPR','0'))))) AS FLAG
I am trying to code it in SQL Server but I am having difficulties. I am using the following in SQL Server:
IIf([TRMSD.TSVSP.SVSX04]='S','HS',IIf([TRMSD.TSVSP.SVSX04]='A','AGE',IIf([TRMSD.TSVSP.SVSX04]='P' And ([TRMSD.SSCSP.SCSPGM]<>'341' And [TRMSD.SSCSP.SCSPGM]<>'371' And [TRMSD.SSCSP.SCSPGM]<>'372'),'PSAV',IIf([TRMSD.TSVSP.SVSX04]='P' And [TRMSD.SSCSP.SCSPGM]='341','CWE',IIf([TRMSD.TSVSP.SVSX04]='P' And [TRMSD.SSCSP.SCSPGM]='371','APPR','0'))))) AS FLAG
Any help would be appreciated, thank you. |
|
|
| |
Topic |
|
|
|
IIF Statement in Access to SQL Server
