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

(billdonovan1947@yahoo.com.au) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

[quoted text, click to view]
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

[quoted text, click to view]

Brilliant! That works. Thanks for all your help.