retrieving data incorrectly from a view using outer join

Hi, all,
I am having this problem with SQLServer 2000. Below is the script to
duplicate the problem. I appreciate if someone can help me on this or
confirm that this is the behavior of current SQLserver version.
Thank you for the help,
Shen
/********* script start ********************/
use northwind
GO
-- create tables and views
create table tbl1 (
newID int,
oldID int,
refID int)
create table tbl2(
newID int,
oldID int,
refID int)
GO
create view v_order
as
select
recordID = a.oldID,
orderID = a.newID,
refID = b.newID
from tbl1 a, tbl2 b
where a.refID *= b.oldID
GO
-- prepare data
insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
insert into tbl2(oldID, newID, refID) values(1, 45280, null)
insert into tbl2(oldID, newID, refID) values(0, null, null)
insert into tbl2(oldID, newID, refID) values(3, 44701, null)
insert into tbl2(oldID, newID, refID) values(4, 44701, null)
insert into tbl2(oldID, newID, refID) values(5, 45827, null)
GO
-- now ready to see the problem
select recordID, orderID, refID from v_order
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
select recordID, orderID, refID from v_order
where refID is null
/**************** result is: ************
1427 210504 NULL
1953 210514 NULL
646 210486 NULL
650 210487 NULL
749 210491 NULL
****************************************
*/
select recordID, orderID, refID from v_order
where refID is not null
/**************** result is: ************
1427 210504 45280
1953 210514 NULL
646 210486 44701
650 210487 44701
749 210491 45827
****************************************
*/
-- clean for this test
drop view v_order
drop table tbl1
drop table tbl2
GO
/*********** script end ******************/I didn't go through your script, but the old outer join syntax has some unex
pected behaviors, this is why it
will be removed in some future release. Did you consider using the modern ou
ter join syntax?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S. Shen" <shshjun@.yahoo.com> wrote in message news:41cf02c.0405190724.748a8fd7@.posting.goog
le.com...
> Hi, all,
> I am having this problem with SQLServer 2000. Below is the script to
> duplicate the problem. I appreciate if someone can help me on this or
> confirm that this is the behavior of current SQLserver version.
> Thank you for the help,
> Shen
> /********* script start ********************/
> use northwind
> GO
> -- create tables and views
> create table tbl1 (
> newID int,
> oldID int,
> refID int)
> create table tbl2(
> newID int,
> oldID int,
> refID int)
> GO
> create view v_order
> as
> select
> recordID = a.oldID,
> orderID = a.newID,
> refID = b.newID
> from tbl1 a, tbl2 b
> where a.refID *= b.oldID
> GO
> -- prepare data
> insert into tbl1(oldID, newID, refID) values(1427 ,210504 ,1)
> insert into tbl1(oldID, newID, refID) values(1953 ,210514 ,0)
> insert into tbl1(oldID, newID, refID) values(646 ,210486 ,3)
> insert into tbl1(oldID, newID, refID) values(650 ,210487 ,4)
> insert into tbl1(oldID, newID, refID) values(749 ,210491 ,5)
> insert into tbl2(oldID, newID, refID) values(1, 45280, null)
> insert into tbl2(oldID, newID, refID) values(0, null, null)
> insert into tbl2(oldID, newID, refID) values(3, 44701, null)
> insert into tbl2(oldID, newID, refID) values(4, 44701, null)
> insert into tbl2(oldID, newID, refID) values(5, 45827, null)
> GO
> -- now ready to see the problem
> select recordID, orderID, refID from v_order
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is null
> /**************** result is: ************
> 1427 210504 NULL
> 1953 210514 NULL
> 646 210486 NULL
> 650 210487 NULL
> 749 210491 NULL
> ****************************************
*/
> select recordID, orderID, refID from v_order
> where refID is not null
> /**************** result is: ************
> 1427 210504 45280
> 1953 210514 NULL
> 646 210486 44701
> 650 210487 44701
> 749 210491 45827
> ****************************************
*/
> -- clean for this test
> drop view v_order
> drop table tbl1
> drop table tbl2
> GO
> /*********** script end ******************/