Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]


select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID]

Author
Message
pcq0125
pcq0125
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 371
Hi,
I am on SQL 2000 and I an having problem with the square parenthesis error on the GROUP BY,. Can sombody help? Thanks.

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID]

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Employee_ID'.
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8668 Visits: 18284
Don't use the alias in the GROUP BY.

You probably want:

select EmpID as [Employee_ID] from dbo.Employee group by EmpID



Or perhaps just simply use the DISTINCT, since you don't seem to be aggregating:


select distinct EmpID as [Employee_ID] from dbo.Employee



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
lucky-80472
lucky-80472
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1367 Visits: 842
pcq0125 (2/20/2008)
Hi,
I am on SQL 2000 and I an having problem with the square parenthesis error on the GROUP BY,. Can sombody help? Thanks.

select [Employee_ID] = EmpID from dbo.Employee group by [Employee_ID]

Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Employee_ID'.



Hello,

It should be written as

select EmpID As Employee_ID from dbo.Employee group by EmpID

There is no need for the square parenthesis and it should be used only when there is a space in between the alias being used for a column.

Hope this is clear.

Thanks


Lucky
ghostrider
ghostrider
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 58
Look at the order of the execution of the statement.

1. Evaluate FROM clause
2. Evaluate WHERE clause
3. Evaluate GROUP BY clause
4. Evaluate HAVING clause
5. Evaluate SELECT clause

Your SELECT clause with the Employee_ID alias goes after the GROUP BY clause that's why it is erroring out on you because there is no way the GROUP BY clause know about you alias. In this case you have to use the real name of the column.
pcq0125
pcq0125
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 371
Hi Matt,
I have some other factors involved in the query to use the alias and below is my original query and how would I resolve the problem with a subquery field? Please advise. Thanks.

select [ASSGNID] = (select ASSGNID from MaintDB..Project (nolock)
where pcode = '11224DER'),
[COUNTRY] = upper(c.FullName),
[Branch NUMBER] = s.Branchno,
[RECORD ID] = d.uid,
[INITIALS] = d.initials,
[DOB] = Customer.dbo.function_DateFormat(d.dob,'mdy','',0),
[EXAMNUM] = d.Custid,
[EXAMDATE] = Customer.dbo.function_DateFormat(v1.Datevisited,'mdy','',0),
[ENTRY] = r.ENTRYno,
[ENTRYDATE] = Customer.dbo.function_DateFormat(v2.Datevisited,'mdy','',0),
[EXAMFAILCAUSE] = v3.CAUSEID,
[EXAMFAILDATE] = Customer.dbo.function_DateFormat(v3.Datevisited,'mdy','',0),
[EARLYTERMCAUSE] = v4.CAUSEID,
[EARLYTERMDATE] = Customer.dbo.function_DateFormat(v4.Datevisited,'mdy','',0),
[VISITRECID] = 'LQ1' + cast(v.VID as nvarchar),
[VISITNAME] = rv.VisitCode,
[VISITCOMPDATE] = Customer.dbo.function_DateFormat(v.Datevisited,'mdy','',0)
from
Customer.dbo.Customer_Geo d (nolock) inner join
MaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner join
MaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left join
Customer.dbo.Customer_ROW r (nolock) on d.Custid = r.Custid left join
(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 1) as v1
on d.Custid = v1.Custid left join
(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 3) as v2
on d.Custid = v2.Custid left join
(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 20) as v3
on d.Custid = v3.Custid left join
(select * from Customer.dbo.Customer_Visited (nolock) where ID_Visit = 21) as v4
on d.Custid = v4.Custid left join
Customer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left join
Customer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visit

order by S.Branchno,d.Custid, v.VID
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8668 Visits: 18284
I'm thinking I wouldn't put the sub-query there, especially since it's returning a single scalar value.

you're also using 4 subqueries when you only need 1. Look at this rewrite (not seeing your data - I made some assumptions):


declare @ASSGNID int
--calculate the scalar
select @ASSGNID=ASSGNID from MaintDB..Project (nolock) where pcode = '11224DER'

select [ASSGNID] = @assgnID, --use the scalar
[COUNTRY] = upper(c.FullName),
[Branch NUMBER] = s.Branchno,
[RECORD ID] = d.uid,
[INITIALS] = d.initials,
[DOB] = Customer.dbo.function_DateFormat(d.dob,'mdy','',0),
[EXAMNUM] = d.Custid,
vst.[EXAMDATE] ,
[ENTRY] = r.ENTRYno,
vst.[ENTRYDATE] ,
vst.[EXAMFAILCAUSE] ,
vst.[EXAMFAILDATE] ,
vst.[EARLYTERMCAUSE],
vst.[EARLYTERMDATE] ,
[VISITRECID] = 'LQ1' + cast(v.VID as nvarchar),
[VISITNAME] = rv.VisitCode,
[VISITCOMPDATE] = Customer.dbo.function_DateFormat(v.Datevisited,'mdy','',0)
from
Customer.dbo.Customer_Geo d (nolock) inner join
MaintDB.dbo.Branch s (nolock) on d.Siteid = s.BranchID inner join
MaintDB.dbo.country c (nolock) on s.countryid = c.CountryID left join
Customer.dbo.Customer_ROW r (nolock) on d.Custid = r.Custid left join
--collapse all 4 subs into just one
(
select CustID,
Customer.dbo.function_DateFormat(
max(case when ID_Visit = 1 then Datevisited else null end)) as EXAMDATE,
Customer.dbo.function_DateFormat(
max(case when ID_Visit = 3 then Datevisited else null end)) as ENTRYDATE,
Customer.dbo.function_DateFormat(
max(case when ID_Visit = 20 then Datevisited else null end)) as EXAMFAILDATE,
Customer.dbo.function_DateFormat(
max(case when ID_Visit = 21 then Datevisited else null end)) as EARLYTERMDATE,
max(case when ID_Visit = 20 then CAUSEID else null end) as EXAMFAILCAUSE,
max(case when ID_Visit = 21 then CAUSEID else null end) as EARLYTERMCAUSE
from Customer.dbo.Customer_Visited (nolock)
where ID_visit in (1,3,20,21)
group by CustID
) vst
on d.Custid = vst.Custid left join
Customer.dbo.Customer_Visited v (nolock) on d.Custid = v.Custid left join
Customer.dbo.Customer_Type rv (nolock) on v.ID_Visit = rv.ID_Visit
order by S.Branchno,d.Custid, v.VID



The assignment syntax to create Aliases is also being deprecated. You probably want to start training yourself not to use it.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
pcq0125
pcq0125
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 371
Thanks for all your help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search