December 11, 2007 at 5:35 am
After I convert to sql2005 and rewrite code to be OK, I don't get the same result.
More description in the code example below which can be executed.
I have to change from "*=" or "=*" which don't work in 2005 to LEFT or RIGHT JOIN.
---************CODE EXAMPLE START***************************************-------------------
use testdb
/*NOTE database must be in MODE80 ( sql2000 ) or you will get ERROR like:
Msg 4147, Level 15, State 1, Line 61
The query uses non-ANSI outer join operators ("*=" or "=*")............
*/
go
set nocount on
create table types
(
typeId int,
trypename varchar(20)
)
insert into types values (1,'type1')
insert into types values (2,'type2')
insert into types values (3,'type3')
insert into types values (4,'type4')
select * from types;
create table typeinfo
(
typeInfoId int,
typeInfoDesc varchar(30),
typeInfoId2 int
)
insert into typeinfo values (1,'typeinfo1',108090)
insert into typeinfo values (2,'typeinfo2',108090)
insert into typeinfo values (3,'typeinfo3',108090)
insert into typeinfo values (1,'typeinfo1',108091)
insert into typeinfo values (2,'typeinfo2',108092)
insert into typeinfo values (3,'typeinfo3',108093)
insert into typeinfo values (4,'typeinfo1',108091)
insert into typeinfo values (5,'typeinfo2',108092)
insert into typeinfo values (3,'typeinfo3',108093)
select * from typeinfo ;
declare @infoNr int;
set @infoNr = 108090;
Print '******RESULT IN SQL2000***********'
--Syntax and result in sql2000 which I want to have in sql2005 but
--there is wrong syntax, error
select typeId, trypename, typeInfoDesc
from types t, typeinfo ti
where typeInfoId2 = @infoNr and t.typeId *= ti.typeInfoId;
Print '******RESULT IN SQL2005***********'
--Rewritten syntax and result in sql2005, result is not the same as in sql2000
--how can I get the same result.
select typeId, trypename, typeInfoDesc
from types t left join typeinfo ti
on t.typeId = ti.typeInfoId
where typeInfoId2 = @infoNr;
drop table types;
drop table typeinfo;
---************CODE EXAMPLE END***************************************-------------------
more info about error I found:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/bb39763e-1197-456b-8d69-e433c88afc4b.htm
December 11, 2007 at 6:37 am
You wanted four rows instead of three, right? Try this:
select typeId, trypename, typeInfoDesc
from types t left join typeinfo ti
on t.typeId = ti.typeInfoId
AND typeinfoId2 = @infoNr
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2007 at 7:08 am
yes thanks a lot.
Best Regards.
/Semko
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply