compatibility level

Sorry here is the query
select Client_Name = rtrim(p.Last_Name) +
Case p.Mid_Name
WHEN null then ' '
WHEN ' ' then ' '
Else ' ' + p.Mid_Name + ' '
End
+ ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id

>--Original Message--
>I recently upgraded my database from 6.5 to 2000. To
take
>advantge of user defined functions I changed the
>compatibility level from 65 to 80. the following query
in
>my code now returns null if the middle name is null. Con
>someone explain wht this is happening and how I can fix?
>Thanks
>.
>
As Sue stated in her post, your query will be affected by the
CONCAT_NULL_YIELDS_NULL setting. To avoid that dependency, or write the
query better overall, you would use
select Client_Name = rtrim(p.Last_Name) +
isnull(p.Mid_Name,' ') + ltrim(p.First_Name)
FROM Client_Info c, Persons p
WHERE c.Client_no = '0004530184'
and c.person_id = p.Person_id
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:173f01c499c1$0b721420$a401280a@.phx.gbl...[vbcol=seagreen]
> Sorry here is the query
> select Client_Name = rtrim(p.Last_Name) +
> Case p.Mid_Name
> WHEN null then ' '
> WHEN ' ' then ' '
> Else ' ' + p.Mid_Name + ' '
> End
> + ltrim(p.First_Name)
> FROM Client_Info c, Persons p
> WHERE c.Client_no = '0004530184'
> and c.person_id = p.Person_id
> take
> in