error 1012: the correlation name '%' has the same exposed name as table '%'.

[Jim]

Im trying to find the error in this statement:

CREATE PROCEDURE STP_selectmain
AS

select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions,
a.reference, art.provenance, sum(c.restorationcost),
sum(d.framingcost), sum(e.cost)
from art as a left outer join artist as b on a.artistid =
b.artistid,
a left outer join restoration as c on a.inventoryid =
c.inventoryid,
a left outer join outframing as d on a.inventoryid =
d.inventoryid,
a left outer join basiccosts as e on a.inventoryid =
e.inventoryid

group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
a.provenance
order by a.inventoryid desc
GO
eveytime I do a syntax check on it I get this error.

error 1012: the correlation name 'a' has the same exposed name as
table 'a'.

Whats the syntax to fix this?

thanks

-Jim

[Erland Sommarskog]

Jim (ji********@motorola.com) writes:

select a.inventoryid, b.firstname, b.lastname, art.title, art.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions,
a.reference, art.provenance, sum(c.restorationcost),
sum(d.framingcost), sum(e.cost)
from art as a left outer join artist as b on a.artistid =
b.artistid,
a left outer join restoration as c on a.inventoryid =
c.inventoryid,
a left outer join outframing as d on a.inventoryid =
d.inventoryid,
a left outer join basiccosts as e on a.inventoryid =
e.inventoryid

group by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,
a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,
a.provenance
order by a.inventoryid desc
GO
...
error 1012: the correlation name 'a' has the same exposed name as
table 'a'.

Whats the syntax to fix this?

You have a mix of old and new FROM syntax, and I would suppose that
you mean:

FROM art AS a
LEFT JOIN artist AS b ON a.artistid = b.artistid
LEFT JOIN restoration AS c ON a.inventoryid = c.inventoryid
LEFT JOIN outframing AS d ON a.inventoryid = d.inventoryid
LEFT JOIN asiccosts AS e on a.inventoryid = e.inventoryid
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp