Issues in Conversion of Astric in SQL 80 Queries t

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 2005 Forums

Transact-SQL (2005)

Issues in Conversion of Astric in SQL 80 Queries t

Author

Topic

Praveen0551
Starting Member

2 Posts

Posted - 2009-02-14 : 11:09:21

Issues in Conversion of Astric in SQL 80 Queries to SQL 90 Compatibilty .

Case 1

We have a query like this in sql 80 :

select *
from tbrokercategories Category,tmenuitem Events,troles tR
Where Category.categoryid =Events.categoryid
And Category.categoryid <> 1
and tR.RoleName = ‘Admin’
and IsEvent= 0
and tR.ItemID=*menuitemid

gives 504 Rows .

When I open this query in SQL Server Management Studio/Query/Design Query in Editor

The query is changed to :

SELECT *
FROM tBrokerCategories AS Category INNER JOIN
tMenuItem AS Events ON Category.CategoryID = Events.CategoryID LEFT OUTER JOIN
tRoles AS tR ON Events.MenuItemID = tR.ItemID
WHERE (Category.CategoryID <> 1) AND (tR.RoleName = 'Admin') AND (tR.IsEvent = 0)

And it gives 302 rows .

If I change the query like this :

SELECT *
FROM tBrokerCategories AS Category
INNER JOIN tMenuItem AS Events ON Category.CategoryID = Events.CategoryID and (Category.CategoryID <> 1)
LEFT OUTER JOIN tRoles AS tR ON Events.MenuItemID = tR.ItemID and (tR.RoleName = 'Admin')
and (tR.IsEvent = 0)

Now it gives 504 rows .

Summary :
1. “SQL Server Management Studio/Query/Design Query in Editor” do not give correct result and
If remove the where clause and and conditions in joins I get correct result .

Case 2

I have query in sql 80 :

SELECT *
FROM tUSERS a,tLOCATIONS b
WHERE USERID='25'
AND ISACTIVE=1
AND a.LOCATIONID *= b.LOCATIONID

It gives 1 row .

When I open this query in SQL Server Management Studio/Query/Design Query in Editor

The query is changed to :

SELECT *
FROM
tUsers AS a
LEFT OUTER JOIN tLocations AS b ON a.LocationID = b.LocationID
WHERE (a.UserID = '25') AND (a.IsActive = 1)

This query gives 1 row .

If I change this query to

SELECT *
FROM
tUsers AS a
LEFT OUTER JOIN tLocations AS b ON a.LocationID = b.LocationID
and (a.UserID = '25') AND (a.IsActive = 1)

it gives 539 rows .

Summary :
1. “SQL Server Management Studio/Query/Design Query in Editor” give correct result and
If remove the where clause and and conditions in joins I do not get correct result .

Problem :

We have around 100 Queries in our code we want to change the compatibility of SQL Server to 90.
What should be the correct method for conversion?
Because in some cases we need to keep the conditions in where clause and in other cases we need to keep conditions in joins.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-14 : 11:19:29

Your method of conversion is correct. Here is why you are seeing different results with LEFT OUTER JOIN:

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources