December 19, 2007 at 11:21 pm
I can't get this query to run i keep gettiing error :-
"The column prefix 'L' does not match with a table name or alias name used in the query."
Any ideas what 'm doing wrong???
SELECT
L.LOC_ID,
L.Location,
L.Phone,
L.DealerCode,
(FirstName + ' ' + LastName) AS Manager,
l.territory_code as storecode,
wcw.store_code
FROM
(select top 1 [store_code] FROM [sampledb].[dbo].[System_User] where [territory_code] = L.territory_code) wcw,
Location L ,
mwc.dbo.Employee E
WHERE
L.Manager_ID *= E.EM_ID
and L.DateClosed IS NULL
and l.loc_id = '109'
and wcw.store_code ='374'
December 20, 2007 at 12:10 am
Hi Patrick,
In the above I refers to which table?
l.territory_code, l.loc_id
Ganapathi M
December 20, 2007 at 5:21 am
thanks for the reply.
it refers to the Location table
thanks in advance
December 20, 2007 at 6:26 am
Hi Patrick,
Please do some changes. Because no alias for 'I'
~~~~~~~~~~~~~~~~~~~~
l.territory_code as storecode,
and l.loc_id = '109'
~~~~~~~~~~~~~~~~~~~~
The above should be
L.territory_code as storecode,
and L.loc_id = '109'
Regards,
Ganapathi M
December 20, 2007 at 9:40 pm
Thanks but that wasn't the problem though
December 20, 2007 at 11:33 pm
I have a feeling that the error's coming from the sub query. Try changing to the following and see if your problem goes away.
SELECT
L.LOC_ID,
L.Location,
L.Phone,
L.DealerCode,
(FirstName + ' ' + LastName) AS Manager,
l.territory_code as storecode,
wcw.store_code
FROM
(select [store_code], [territory_code] FROM [sampledb].[dbo].[System_User]) wcw INNER JOIN
Location L ON WCW.[territory_code] = L.territory_code ,
mwc.dbo.Employee E
WHERE
L.Manager_ID *= E.EM_ID
and L.DateClosed IS NULL
p.s. you should consider changing the *= to use the outer join syntax, as the older join style is deprecated in SQL 2000
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2007 at 5:51 am
Although i fixed it.....but i will try your advice and let u know.
Thanks
December 21, 2007 at 6:00 am
What was the problem?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply