Concatenating with stuff causing issue

• tshad

  SSCertifiable

  Points: 5905

  More actions

  April 19, 2018 at 4:41 pm

  #344793

  I have a query that is doing some grouping and I needed to concatenate a column so I don't lose the remarks.

  I can make it work fine, until I add a join.  Then I start getting errors about variables not in the group by clause when in fact they are.

  Here is the one that works:
  
  
  Now I add a join so I can get the employee name and status.  I am not adding those columns yet, as I get the error just adding the join and aliases. 
  

  

  Now I get this message:
  

  Msg 8120,Level 16, State 1, Line 28

  Column'Benefits.BenefitsInvoice.EmployeeID' is invalid in the select list because itis not contained in either an aggregate function or the GROUP BY clause.

  It is pointing at the line in the Stuff statement:

  WHERE

  bij.EmployeeID=t2.EmployeeID

  Why would this cause an error?

  Thanks,
  Tom

• tshad

  SSCertifiable

  Points: 5905

  More actions

  April 19, 2018 at 4:55 pm

  #1987610

  I got it to work.

  All I did was change the where clause alias from:
  WHERE bij.EmployeeID=t2.EmployeeID
  to:
  WHERE e.EmployeeID=t2.EmployeeID

  Not sure why that mattered or why the employee go the error.

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  April 20, 2018 at 10:23 am

  #1987704

  tshad - Thursday, April 19, 2018 4:55 PM

  I got it to work.

  All I did was change the where clause alias from:
  WHERE bij.EmployeeID=t2.EmployeeID
  to:
  WHERE e.EmployeeID=t2.EmployeeID

  Not sure why that mattered or why the employee go the error.

  The problem was that you were grouping by e.EmployeeID, so touching the bij alias without adding it to the GROUP BY, even in a sub-query, is a no-no.   You just can't do it.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)