Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select [Employee_ID] = EmpID from dbo.Employee GROUP BY [Employee_ID] Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2008 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 7:34 AM
Points: 100, Visits: 317
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'.

Post #457980
Posted Wednesday, February 20, 2008 7:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
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?
Post #457983
Posted Wednesday, February 20, 2008 7:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, January 10, 2014 3:03 PM
Points: 1,295, Visits: 752
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
Post #457986
Posted Wednesday, February 20, 2008 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 30, 2012 9:08 AM
Points: 7, 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.
Post #457989
Posted Wednesday, February 20, 2008 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 7:34 AM
Points: 100, Visits: 317
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
Post #458000
Posted Wednesday, February 20, 2008 8:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
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?
Post #458010
Posted Wednesday, February 20, 2008 10:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 12, 2014 7:34 AM
Points: 100, Visits: 317
Thanks for all your help
Post #458119
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse