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]