Qyery returns different results on SQL server 7.0 and 2000.

SELECT CASE
WHEN ISNUMERIC(t.clnum)=1 THEN
(SELECT CASE
WHEN PropertyAddressOptionCode =
'F' THEN 1
WHEN PropertyAddressOptionCode =
'SZ' THEN 2
ELSE -10
END
FROM TableA (NOLOCK) WHERE Loannumber = cast(t.clnum as int))
ELSE -1
END AS 'PropertyAddressOptionType'
From TableB t where t.lnum='xyz'
The scenarios is that TableA does not having a matching value returned by
TableB.
In such scenario the ideally output should be null.
This is waht we get in 2000 but on sql 7.0 we get -10 as output.
Any ideas as why it that happens like that on 7.0.
I have a solution to handle this, but i'm interested in why that is happenin
g.
The above query returns -10 valueTry adding SET ANSI_NULLS ON to the start of your batch and re-run on both
servers.
It could be that the default setting of ANSI_NULLS differs between v7 and
2000
HTH. Ryan
"Manoj9" <Manoj9@.discussions.microsoft.com> wrote in message
news:24E5D921-3E10-4F8B-91F0-0B35EBDF53C3@.microsoft.com...
> SELECT CASE
> WHEN ISNUMERIC(t.clnum)=1 THEN
> (SELECT CASE
> WHEN PropertyAddressOptionCode =
> 'F' THEN 1
> WHEN PropertyAddressOptionCode =
> 'SZ' THEN 2
> ELSE -10
> END
> FROM TableA (NOLOCK) WHERE Loannumber = cast(t.clnum as int))
> ELSE -1
> END AS 'PropertyAddressOptionType'
> From TableB t where t.lnum='xyz'
> --
> The scenarios is that TableA does not having a matching value returned by
> TableB.
> In such scenario the ideally output should be null.
> This is waht we get in 2000 but on sql 7.0 we get -10 as output.
> Any ideas as why it that happens like that on 7.0.
> I have a solution to handle this, but i'm interested in why that is
> happening.
> The above query returns -10 value
>