Prev: xml.value xquery on element centric xml parameter
Next: SQL Server 2000 log shipping monitor
From: tnichols333 via SQLMonster.com on 2 Dec 2009 13:37
I get the following error message when I execute:

The multi-part identifier "Goal.SalesPersonId" could not be bound.


SELECT sp.FirstName + ' ' + sp.LastName AS SalesPersonName,
g.goalid,
g.goaltype,
g.goalamount,
g.beginingdate,
g.endingdate,
(SELECT COUNT(customerid) FROM Customer WHERE Acquired = 'Yes'
AND CONVERT(SMALLDATETIME, AcquiredDate, 101) BETWEEN g.
BeginingDate AND g.EndingDate
AND Customer.SalesPersonId = Goal.SalesPersonId) AS GoalAcquired,

(SELECT SUM(grossprofit) FROM Customer WHERE Acquired = 'Yes'
AND CONVERT(SMALLDATETIME, AcquiredDate, 101) BETWEEN g.
BeginingDate AND g.EndingDate
AND Customer.SalesPersonId = Goal.SalesPersonId) AS GrossProfit
FROM Goal AS g
INNER JOIN SalesPerson AS sp
ON g.SalesPersonId = sp.SalesPersonId
WHERE g.BeginingDate < DATEADD(DAY, DATEDIFF(DAY, '20010101',
CURRENT_TIMESTAMP), '20010102')
AND g.EndingDate >= DATEADD(DAY, DATEDIFF(DAY, '20010101',
CURRENT_TIMESTAMP), '20010101')
GROUP BY sp.FirstName + ' ' + sp.LastName,
g.goalid,
g.goaltype,
g.goalamount,
g.beginingdate,
g.endingdate;

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1

From: Plamen Ratchev on 2 Dec 2009 14:00
Since you alias the table Goal as "g", you have to use the alias to reference columns in the query. Simply replace
Goal.SalesPersonId with g.SalesPersonId.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tnichols333 via SQLMonster.com on 2 Dec 2009 15:18
THANK YOU - THANK YOU - THANK YOU!!!!

Plamen Ratchev wrote:
>Since you alias the table Goal as "g", you have to use the alias to reference columns in the query. Simply replace
>Goal.SalesPersonId with g.SalesPersonId.
>

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1

 | 
Pages: 1
Prev: xml.value xquery on element centric xml parameter
Next: SQL Server 2000 log shipping monitor