compatibility level

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?
ThanksSorry 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
>.
>|||In what query? If I were to guess, most likely you are using string
concatenation to build a name. Concatenate null yields null is off by
default as this is ANSI standard. You can find more info in books
online if you look up CONCAT_NULL_YIELDS_NULL
You can change your query to handle the null values in building the
string - if that's what the issue is.
-Sue
On Mon, 13 Sep 2004 11:32:23 -0700, "steve"
<anonymous@.discussions.microsoft.com> wrote:
>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...
> 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
> >.
> >