How to concatenate field names ?

<i><b>Originally posted by : Padmaja Tallavajhala (coolpersona@hotmail.com)</b></i><br />I have this query that pulls the names of all the patients.<br /><br />SELECT Patient.oid, Person.last_name + ' , ' + Person.first_name + ' ' + Person.middle_name<br /> AS Patient_Name <br />FROM Patient INNER JOIN<br /> Person ON Patient.oid = Person.oid<br />order by Patient_Name<br /><br />But I get several rows where the Patient_Name Alias has null values because the Patient.Middle_Name is null. When we contatenate three fields where one field is null, then does the result also return a null value ? It looks like it does.<br /><br />So to prevent this, what should I do ?<br />

<i><b>Originally posted by : Graham Masters (graham_masters@hp.com)</b></i><br />NULL means "not known" so when ever a NULL value is combined with a known values via math or string operations the result is "not known" or NULL.<br /><br />To solve, use a CASE stmt in your SELECT e.g.<br /><br />SELECT ... +<br />CASE WHEN Person.middle_name IS NULL THEN "" ELSE<br /> " " + Person.middle_name END ... FROM<br /><br /><br />Graham<br /><br /><br />------------<br />Padmaja Tallavajhala at 7/10/2001 10:22:29 AM<br /><br />I have this query that pulls the names of all the patients.<br /><br />SELECT Patient.oid, Person.last_name + ' , ' + Person.first_name + ' ' + Person.middle_name<br /> AS Patient_Name <br />FROM Patient INNER JOIN<br /> Person ON Patient.oid = Person.oid<br />order by Patient_Name<br /><br />But I get several rows where the Patient_Name Alias has null values because the Patient.Middle_Name is null. When we contatenate three fields where one field is null, then does the result also return a null value ? It looks like it does.<br /><br />So to prevent this, what should I do ?<br />

<i><b>Originally posted by : Deepu (medeepu@rediffmail.com)</b></i><br />Hi padmaja<br /><br />You can change the Query as follows, using ISNULL function..it is cool and easy.<br /><br />SELECT Patient.oid, PatientName = ISNULL(Person.last_name,'')+''+ISNULL(Person.middl e_name,'') + ''+ISNULL(Person.first_name,'') FROM Patient INNER JOIN<br />Person ON Patient.oid = Person.oid<br />order by Patient_Name<br /><br /><br /><br /><br />SELECT ... +<br />CASE WHEN Person.middle_name IS NULL THEN "" ELSE<br /> " " + Person.middle_name END ... FROM<br /><br /><br />Graham<br /><br /><br />------------<br />Padmaja Tallavajhala at 7/10/2001 10:22:29 AM<br /><br />I have this query that pulls the names of all the patients.<br /><br />SELECT Patient.oid, Person.last_name + ' , ' + Person.first_name + ' ' + Person.middle_name<br /> AS Patient_Name <br />FROM Patient INNER JOIN<br /> Person ON Patient.oid = Person.oid<br />order by Patient_Name<br /><br />But I get several rows where the Patient_Name Alias has null values because the Patient.Middle_Name is null. When we contatenate three fields where one field is null, then does the result also return a null value ? It looks like it does.<br /><br />So to prevent this, what should I do ?<br />