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

  • 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'.

  • 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?

  • 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

  • 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.

  • 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

  • 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?

  • 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