Msg 107, Level 16, State 2, Line 1

[billdonovan1947]

Hi,

I am getting the following error from the query below against SQL
Server 8.00.2039 (SP4)

Error:
====
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'd' does not match with a table name or alias name
used in the query.

Select Statement:
=============
select ....
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
products d,
limit_types e
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID

I know this was a known bug in MS-SQL7, but I thought it had been fixed
in 2000.

Can anyone help?

Thanks

[Erland Sommarskog]

(bi*************@yahoo.com.au) writes:

I am getting the following error from the query below against SQL
Server 8.00.2039 (SP4)

Error:
====
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'd' does not match with a table name or alias name
used in the query.

Select Statement:
=============
select ....
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
products d,
limit_types e
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID

I know this was a known bug in MS-SQL7, but I thought it had been fixed
in 2000.

Without seeing the entire query, it's difficult to see what might be
wrong, but try rewrite the query to use JOIN syntax throughout:

SELECT ...
FROM trades a
JOIN gems_product_groups b ON ...
JOIN portfolio_nav_mapping c ON ...
JOIN products d ON ...
JOIN limit_types e ON ...
LEFT JOIN gems_prod_trade_mod f on d.product_id = ...


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Malcolm]

>From what I've observed, "left join" requires the tables that you're
joining to be adjacent in the list, at least when mixed with other
joins written the way you have them. In your case you have limit_types
between products and the left join. If you simply swap the positions of
products and limit_types then it should work. e.g:
select ....
from trades a,
gems_product_groups b,
portfolio_nav_mapping c,
limit_types e,
products d
left outer join gems_prod_trade_mod f on d.product_id =
f.product_ID

Malcolm

[billdonovan1947]

>>>From what I've observed, "left join" requires the tables that you're

joining to be adjacent in the list, at least when mixed with other
joins written the way you have them.

Brilliant! That works. Thanks for all your help.