The reason being is when you do a calculation with a NULL as a value in
the calculation, the result will always be NULL.

Try this:

-----------------------------------------------------------------------------

Select Client_Admissions.Referral_Date, Client_Admissions.Admit_Date,
Client_Admissions.Discharge_Date, Client_Admissions.Close_Date,
Case When Clients.FirstName Is Null or Clients.FirstName = '' Then
Case When Clients.LastName Is Null or Clients.LastName = '' Then
Case When Clients.MiddleName Is Null or Clients.MiddleName = '' Then
''
Else
Clients.MiddleName
End
Else
Case When Clients.MiddleName Is Null or Clients.MiddleName = '' Then
Clients.LastName
Else
Clients.LastName || ', ' || Clients.MiddleName
End
End
Else
Case When Clients.LastName Is Null or Clients.LastName = '' Then
Case When Clients.MiddleName Is Null or Clients.MiddleName = '' Then
Clients.FirstName
Else
Clients.FirstName || Clients.MiddleName
End
Else
Case When Clients.MiddleName Is Null or Clients.MiddleName = '' Then
Clients.LastName || ', ' || Clients.FirstName
Else
Clients.LastName || ', ' || Clients.FirstName || ' ' ||
Clients.MiddleName
End
End
End As Name
From Clients Inner Join Client_Admissions on Clients.Client_ID =
Client_Admissions.Admission_ID
Order by 5;

-----------------------------------------------------------------------------

Also, you may simply want to add a computed field to the Clients table
(this is what I do):

-----------------------------------------------------------------------------

Alter Table Clients
Add MilitaryName Computed By
(
Case When FirstName Is Null or FirstName = '' Then
Case When LastName Is Null or LastName = '' Then
Case When MiddleName Is Null or MiddleName = '' Then
''
Else
MiddleName
End
Else
Case When MiddleName Is Null or MiddleName = '' Then
LastName
Else
LastName || ', ' || MiddleName
End
End
Else
Case When LastName Is Null or LastName = '' Then
Case When MiddleName Is Null or MiddleName = '' Then
FirstName
Else
FirstName || MiddleName
End
Else
Case When MiddleName Is Null or MiddleName = '' Then
LastName || ', ' || FirstName
Else
LastName || ', ' || FirstName || ' ' || MiddleName
End
End
End
)

-----------------------------------------------------------------------------


Jason

Clay Shannon wrote:

> With this SQL:
>
>
>
> SELECT
>
> A.REFERRAL_DATE, A.ADMIT_DATE, A.DISCHARGE_DATE, A.CLOSE_DATE,
>
> C.LASTNAME || ', ' || C.FIRSTNAME || ' ' || C.MIDDLENAME AS NAME,
> C.CLIENT_STATE
>
> FROM
>
> CLIENTS C
>
> JOIN CLIENT_ADMISSIONS A ON C.CLIENT_ID = A.ADMISSION_ID
>
> WHERE ((C.FIRSTNAME IS NOT NULL) AND (C.MIDDLENAME IS NOT NULL) AND
> (C.LASTNAME IS NOT NULL)) AND
>
> A.REFERRAL_DATE IS NOT NULL
>
> ORDER BY C.LASTNAME, REFERRAL_DATE DESC
>
>
>
> I get only records for clients who have values for all three names (first,
> middle, last). And that makes sense.
>
>
>
> However, if I remove the "AND (C.MIDDLENAME IS NOT NULL)", I get a lot of
> records with "<null>" as the NAME value. What I want is to see name values
> such as:
>
>
>
> Alvin Lee
>
> Robert E. Lee
>
> Rob Roy
>
> Blackbird Crow Raven
>
>
>
> IOW, at least a first and last name, but a middle initial or name if one
> exists in the database.
>
>
>
> What need I do to only return records with first and last names, but to also
> display the middle name when there IS a middle name?
>
>
>
> Clay Shannon,
>
> Dimension 4 Software
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>