The multi-part identifier "e.updatedon" could not

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

The multi-part identifier "e.updatedon" could not

Author

Topic

usmansyedali
Starting Member

5 Posts

Posted - 2010-02-28 : 05:18:55

Hi..
I tried to execute this query:

select 'PREVASSIGN', u.username as userid, e.updatedon,

(
select count(*)
from tblmivdet E,tblmivs M, tblbudget b
where e.prjid=b.prjid and e.csicode=b.bcode and E.prjid=542
and E.prjid=M.prjid and E.isno=M.isno and M.issdate<='2009-12-31'
and ltrim(rtrim(isnull(e.csicode,''))) <> '' and u.userid = substring(E.updatedby,1,
case
when charindex('<->', E.updatedby)-1<=0
then 1
else charindex('<->', E.updatedby)-1 end ) and
e.updatedon <
dateadd(day, 0, cast(DATEPART(yyyy, getdate()) as varchar(5)) + '-' + cast(DATEPART(m, getdate()) as varchar(5)) + '-' + cast(DATEPART(d, getdate()) as varchar(5)))
group by u.username,e.updatedon

)

from users u
--------------
but I am getting the following error:
The multi-part identifier "e.updatedon" could not be bound.

I would be glad if someone can help me resolve this error...

Kristen
Test

22859 Posts

Posted - 2010-02-28 : 06:04:44

"select 'PREVASSIGN', u.username as userid, e.updatedon"

E cannot be used as an alias here as it is not in scope.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 08:39:43

which table is updatedon column contained?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

usmansyedali
Starting Member

5 Posts

Posted - 2010-02-28 : 08:56:19

Thanx for identifying the mistake kristen :D... I just want to know how can I get the desired result that I have mentioned in my query? If Alias e is out of scope how can I get updatedon in my select list??

Thanx

usmansyedali
Starting Member

5 Posts

Posted - 2010-02-28 : 08:59:27

updatedon is a field that is contained in table "tblmivdet" with alias E which in part of an inner query...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 09:06:39

[code]select 'PREVASSIGN', u.username as userid, u1.updatedon,u1.cnt
from users u
cross apply
(
select u.username,e.updatedon,count(*) as cnt
from tblmivdet E,tblmivs M, tblbudget b
where e.prjid=b.prjid and e.csicode=b.bcode and E.prjid=542
and E.prjid=M.prjid and E.isno=M.isno and M.issdate<='2009-12-31'
and ltrim(rtrim(isnull(e.csicode,''))) <> '' and u.userid = substring(E.updatedby,1,
case
when charindex('<->', E.updatedby)-1<=0
then 1
else charindex('<->', E.updatedby)-1 end ) and
e.updatedon <
dateadd(day, 0, cast(DATEPART(yyyy, getdate()) as varchar(5)) + '-' + cast(DATEPART(m, getdate()) as varchar(5)) + '-' + cast(DATEPART(d, getdate()) as varchar(5)))
group by u.username,e.updatedon

)u1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

usmansyedali
Starting Member

5 Posts

Posted - 2010-03-01 : 01:52:11

Thank you very much for your time...:D... when I ran your query I got the following error:

Each GROUP BY expression must contain at least one column that is not an outer reference.

"i am not getting it"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-03-01 : 04:02:22

the "Group By" clause refers to a column aliaised by "u"- u.username. This is not in the subquery. Try getting the subquery working on it's own.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-01 : 10:32:40

looking at your query i dont think you need u.username field in inner query


select 'PREVASSIGN', u.username as userid, u1.updatedon,u1.cnt
from users u
cross apply
(
select e.updatedon,count(*) as cnt
from tblmivdet E,tblmivs M, tblbudget b 
where e.prjid=b.prjid and e.csicode=b.bcode and E.prjid=542	
and E.prjid=M.prjid and E.isno=M.isno and M.issdate<='2009-12-31' 
and ltrim(rtrim(isnull(e.csicode,''))) <> '' and u.userid = substring(E.updatedby,1,
case 
when charindex('<->', E.updatedby)-1<=0 
then 1 
else charindex('<->', E.updatedby)-1 end ) and 
e.updatedon <
dateadd(day, 0, cast(DATEPART(yyyy, getdate()) as varchar(5)) + '-' + cast(DATEPART(m, getdate()) as varchar(5)) + '-' + cast(DATEPART(d, getdate()) as varchar(5)))
group by e.updatedon

)u1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources