convert query from SQL2000 to 2005

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)

convert query from SQL2000 to 2005

Author

Topic

philiptaylor
Starting Member

2 Posts

Posted - 2008-12-17 : 17:28:02

specifically, outer join syntax failing post migration :
I've been bogged down on this for a while, the issue seems to be using the same table w/ multiple outer joins.

need to be explicit w/ outer join syntax :

SELECT DISTINCT '[FUNCTION_CODE]', a.id, 12, e.id, 0,
CASE
WHEN ISNULL(t.amount,9876543210)=9876543210 THEN 1 ELSE 0
END
FROM ct_account a, ct_pk0093 t, ct_0000_adjtype b, ct_entity e
WHERE e.id *= t.entity
AND a.ct_0000_adjlgtype = b.id
AND a.id *= t.accnt
AND b.name = 'ENTBAL02'
AND t.flow = 193 /* RX-011 */
AND t.ct_0000_ca = 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-17 : 17:34:45

Instead of *=, you'll need to use OUTER JOIN syntax.

Here's an example:

SELECT ...
FROM Table1 t1
LEFT OUTER JOIN Table2 t2
ON t1.Column1 = t2.Column1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

philiptaylor
Starting Member

2 Posts

Posted - 2008-12-17 : 17:36:36

I'm sorry, should have been more clear. Yes, I understand need to use "LEFT OUTER" or "RIGHT OUTER" explicit syntax - but I'm having issues getting same results once I've converted. My I'm wondering if my issues are that I'm trying to use the same table in multiple outer joins...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-17 : 17:42:10

Without seeing some sample data or a description, it's hard to know what order to put these tables in so that we can use LEFT JOIN. Here's my try:


SELECT DISTINCT 
	'[FUNCTION_CODE]', 
	a.id, 
	12, 
	e.id, 
	0,
	CASE
		WHEN ISNULL(t.amount,9876543210)=9876543210 THEN 1 
		ELSE 0
	END
FROM ct_entity e
LEFT OUTER JOIN ct_pk0093 t
ON e.id = t.entity
LEFT OUTER JOIN ct_account a
ON a.id = t.accnt
INNER JOIN ct_0000_adjtype b
ON a.ct_0000_adjlgtype = b.id
WHERE 
	b.name = 'ENTBAL02' AND
	t.flow = 193 /* RX-011 */ AND
	t.ct_0000_ca = 0

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources