May 20, 2008 at 6:13 am
The query uses non-ANSI outer join operators ("*=" or "=*").
To run this query without modification, please set the compatibility level for current database to 80 or lower,
using stored procedure sp_dbcmptlevel.
It is strongly recommended to rewrite the query using
ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN).
In the future versions of SQL Server, non-ANSI join operators will not be supported even
in backward-compatibility modes.
May 20, 2008 at 8:26 am
I'm not sure why this was set up as a poll. If what you're trying to do is get advice on how to fix this error, I'd rewrite the joins to be ANSI-92 standard (use outer joins). Books Online has data on exactly how to do this, but here's a sample:
select *
from dbo.Table1,
dbo.Table2
where Table1.Col1 *= Table2.Col1
changes to:
select *
from dbo.Table1
left outer join dbo.Table2
on Table1.Col1 = Table2.Col1
The first one is "the old way", the second one is the ANSI-92 ("new") way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 10:41 pm
this query Executing fine and displayed currect output in SQL2000
SELECT *
FROM tbl_dxu_entitydetails WITH (NOLOCK), tbl_dxu_templatedetails WITH (NOLOCK), tbl_DXU_LookUp WITH (NOLOCK)
WHERE tbl_dxu_entitydetails.FieldID *= tbl_dxu_templatedetails.ActualFieldId
AND tbl_dxu_entitydetails.FieldID *= tbl_DXU_LookUp.DestinationFieldID
AND tbl_dxu_entitydetails.EntityID = (SELECT EntityId
FROM tbl_DXU_TemplateMaster WITH (NOLOCK)
WHERE tbl_DXU_TemplateMaster.TemplateID = 24)
I have converted this query for SQL2005 Like this
SELECT *
FROM tbl_dxu_entitydetails WITH (NOLOCK)
left OUTER JOIN tbl_dxu_templatedetails WITH (NOLOCK)
ON tbl_dxu_entitydetails.FieldID = tbl_dxu_templatedetails.ActualFieldId
left OUTER JOIN tbl_DXU_LookUp WITH (NOLOCK)
on tbl_dxu_entitydetails.FieldID = tbl_DXU_LookUp.DestinationFieldID
WHERE
tbl_dxu_entitydetails.EntityID = (SELECT EntityId
FROM tbl_DXU_TemplateMaster WITH (NOLOCK)
WHERE tbl_DXU_TemplateMaster.TemplateID = 24)
Both the query displayed the currect output in SQL2000 and SQL 2005 but when i put filter condition like this
******************************************
AND tbl_dxu_templatedetails.TemplateID = 24
AND tbl_DXU_LookUp.TemplateID = 24
AND Show = 1
ORDER BY tbl_dxu_entitydetails.IsMandatory DESC, FieldID ASC
**************************************************
Then its displayed wrong output..........
So please help me..................
May 21, 2008 at 9:22 am
These two:
AND tbl_dxu_templatedetails.TemplateID = 24
AND tbl_DXU_LookUp.TemplateID = 24
Need to be in the Join criteria, not in Where clause. I'm not sure about the Show=1 part, since I don't know which table that's in. If it's in tbl_dxu_entitydetails, it's fine. If it's in one of the tables you are left joining to, it needs to be in the Join clause (after "On").
Something like this is what you'll end up with:
SELECT *
FROM tbl_dxu_entitydetails WITH (NOLOCK)
LEFT OUTER JOIN tbl_dxu_templatedetails WITH (NOLOCK)
ON tbl_dxu_entitydetails.FieldID = tbl_dxu_templatedetails.ActualFieldId
AND tbl_dxu_templatedetails.TemplateID = 24
LEFT OUTER JOIN tbl_DXU_LookUp WITH (NOLOCK)
ON tbl_dxu_entitydetails.FieldID = tbl_DXU_LookUp.DestinationFieldID
AND tbl_DXU_LookUp.TemplateID = 24
AND tbl_dxu_entitydetails.FieldID is not null
WHERE
tbl_dxu_entitydetails.EntityID =
(SELECT EntityId
FROM tbl_DXU_TemplateMaster WITH (NOLOCK)
WHERE tbl_DXU_TemplateMaster.TemplateID = 24)
AND Show = 1
ORDER BY tbl_dxu_entitydetails.IsMandatory DESC, FieldID ASC
Just make sure that "Show = 1" is in the right part, depending on which table it's in.
The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 21, 2008 at 9:28 am
GSquared (5/21/2008)
The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.
methinks you meant to say
....any criteria on the right table...
Of course - that statement holds true unless your criteria allows for the fact that the RIGHT table columns might be null (in which case the optimizer honors the LEFT OUTER JOIN).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 10:33 am
Matt Miller (5/21/2008)
GSquared (5/21/2008)
The reason for this is that, in a Left Outer Join, any criteria on the left table need to be in the Join clause, or they make it into an Inner Join.
methinks you meant to say
....any criteria on the right table...
Of course - that statement holds true unless your criteria allows for the fact that the RIGHT table columns might be null (in which case the optimizer honors the LEFT OUTER JOIN).
You're correct. I meant the right table.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply