February 20, 2008 at 7:33 am
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'.
February 20, 2008 at 7:42 am
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?
February 20, 2008 at 7:44 am
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
February 20, 2008 at 7:45 am
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.
February 20, 2008 at 7:56 am
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
February 20, 2008 at 8:18 am
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?
February 20, 2008 at 10:44 am
Thanks for all your help
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy