ISNULL and COALESCE produce diff result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ISNULL and COALESCE produce diff result

Author

Topic

cas_o
Posting Yak Master

154 Posts

Posted - 2008-07-24 : 05:38:43

[code]create table tbPrimary
(
dbPk int identity(1,1) primary key,
dbPAttr varchar(32) NOT NULL
)
GO

create table tbForeign
(
dbFk int not null,
dbFAttr varchar(32) not null,

)
GO

insert into tbPrimary(dbPAttr)
select 'P_One'
UNION ALL
select 'P_Two'
UNION ALL
select 'P_Three'
UNION ALL
select 'P_Four'
UNION ALL
select 'P_Five'
GO

insert into tbForeign(dbFk, dbFAttr)
select 1, 'F_One'
UNION ALL
select 1, 'F_Two'
UNION ALL
select 2, 'F_One'
UNION ALL
select 3, 'F_One'
GO

select
pk.dbPAttr,
isnull(sub.FKAttr, 'No FK Attr') as FKAttr1,
coalesce(sub.FKAttr, 'No FK Attr') as FKAttr2
from
tbPrimary pk
left join
(
select distinct
p.dbPk,
'Has FK Attr' as FKAttr
from
tbPrimary p
join tbForeign f on p.dbPk = f.dbFk
) sub on pk.dbPk = sub.dbPk
order by
pk.dbPK

SQL Server 2000 - RESULTS :

dbPAttr FKAttr1 FKAttr2
-------------------------------- ----------- -----------
P_One Has FK Attr Has FK Attr
P_Two Has FK Attr Has FK Attr
P_Three Has FK Attr Has FK Attr
P_Four Has FK Attr No FK Attr
P_Five Has FK Attr No FK Attr

(5 row(s) affected)

SQL Server 2005 RESULTS :

dbPAttr FKAttr1 FKAttr2
-------------------------------- ----------- -----------
P_One Has FK Attr Has FK Attr
P_Two Has FK Attr Has FK Attr
P_Three Has FK Attr Has FK Attr
P_Four No FK Attr No FK Attr
P_Five No FK Attr No FK Attr

(5 row(s) affected)

[/code]

wtf?

;-]... Quack Waddle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:15:03

I get correct result for both 2000 and 2005.

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
	May  3 2005 23:18:38 
	Copyright (c) 1988-2003 Microsoft Corporation
	Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2005 - 9.00.3233.00 (Intel X86) 
	Mar  6 2008 22:09:47 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

E 12°55'05.25"
N 56°04'39.16"

cas_o
Posting Yak Master

154 Posts

Posted - 2008-07-24 : 06:21:14

Aha! I have developer edition of 2000 but with same service pack.

Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

My 2005 installation is:

Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)
Feb 26 2008 18:15:01
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

cas_o AKA Holly Styles, Paul Alcon;-]... Quack Waddle

cas_o
Posting Yak Master

154 Posts

Posted - 2008-07-24 : 06:21:55

I still think this sucks though :)

cas_o AKA Holly Styles, Paul Alcon;-]... Quack Waddle

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-24 : 06:24:19

No, you have no service pack installed for your SQL Server 2000 Developer Edition. You are still on 194.
Latest service pack for SQL Server 2000 is SP4 and can be found here
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&displaylang=en

Install SP4 and try again.

E 12°55'05.25"
N 56°04'39.16"

cas_o
Posting Yak Master

154 Posts

Posted - 2008-07-24 : 07:02:06

[code]Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

SQL Server 2000 RESULTS :

dbPAttr FKAttr1 FKAttr2
-------------------------------- ----------- -----------
P_One Has FK Attr Has FK Attr
P_Two Has FK Attr Has FK Attr
P_Three Has FK Attr Has FK Attr
P_Four No FK Attr No FK Attr
P_Five No FK Attr No FK Attr

(5 row(s) affected)[/code]

Sorted. Thanks!

cas_o AKA: Holly Styles, Paul Alcon ;-]... Quack Waddle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources