Only put a period "." if there is a middle intial.

I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)

I need my resultant field data to look like the following:
"Doe, John P."

I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".

I have tried the following CASE statement:

CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END

However, I get an error indicating that the CASE statement is not supported in the Query Designer.

How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?Do you mean that you get an error in Query Analyzer? Or some other tool? CASE statements most assuredly are supported in Query Analyzer.

This code works fine for me:

CASE
 WHEN middleinitial IS NOT NULL THEN ' ' + middleinitial + '.'
 ELSE ''
END

What is the complete query you're trying to build?

Don|||I found my answer on a different forum. For those who are interested...

If you SET CONCAT_NULL_YIELDS_NULL ON (which I believe is the default value), you can accomplish the task in the following way:

SELECT LastName + ', ' + FirstName + ISNULL(' ' + MiddleInitial + '.', '')
FROM MyTable|||donkeily,

I am sorry, I did not see your post. I must have been posting at about the same time as you.

I was attempting to do use the CASE statement in the Query Designer. I wonder why the CASE statement is supported in the Query Analyzer, but not in the Query Designer? Seems strange to me.|||Hmm. That is bizzare. I didn't know QD didn't support it. Ick!

Don