Invalid column name 'Homelaborlevelnm4'

Databases
1

Hello While i am trying to run below sql for my report ,I am getting error for "Invalid column name ‘Homelaborlevelnm4’. "
Below is the sql

SELECT EmployeeId 'EmpId',
		LaborLevelName4 'Plant',
		LaborLevelDsc3	'DeptName',
		LaborLevelName6 'Employeetype',
                PERSONFULLNAME  'Employee',
                SUBSTRING(HOMELABORLEVELNM4,1,6) 'Dept' ,
                SUBSTRING(HOMELABORLEVELNM4,7,1) 'SHIFT1',
                ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours',
		ROUND(SUM(WageAmount),2) 'Earnings'
FROM Totals
WHERE ApplyDate BETWEEN @from_timeframe AND @to_timeframe AND 
PayCodeName = 'Regular'
GROUP BY EmployeeId, Homelaborlevelnm4, LaborLevelDsc3, LaborLevelName6,LaborLevelName4,PERSONFULLNAME)
,
Hours_Earnings AS 
(SELECT EmployeeId 'EmpId',
		LaborLevelName6 'Employeetype',
                SUBSTRING(HOMELABORLEVELNM4,1,6) 'DEPT',
                ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours',
		ROUND(SUM(WageAmount),2) 'Earnings
2

That means that Homelaborlevelnm4 is not on your totals table.

use a show create table Totals to see what the columns are.

3

It is available.

4

Ah. Should have caught it.

The fields you have in your GROUP BY should be in your SELECT statement. HOMELABORLEVELNM4 is in the group by but not in the SELECT. It’s substringed, but the whole column is not selected.

The top portion should be

SELECT EmployeeId 'EmpId'
     , LaborLevelName4 'Plant'
     , LaborLevelDsc3	'DeptName'
     , LaborLevelName6 'Employeetype'
     , PERSONFULLNAME 'Employee'
     , SUBSTRING(HOMELABORLEVELNM4,1,6) 'Dept' 
     , SUBSTRING(HOMELABORLEVELNM4,7,1) 'SHIFT1'
     , ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours'
     , ROUND(SUM(WageAmount),2) 'Earnings'
  FROM Totals
 WHERE ApplyDate BETWEEN @from_timeframe AND @to_timeframe 
   AND PayCodeName = 'Regular'
 GROUP BY EmployeeId
     , LaborLevelName4
     , LaborLevelDsc3
     , LaborLevelName6
     , PERSONFULLNAME
     , SUBSTRING(HOMELABORLEVELNM4,1,6)
     , SUBSTRING(HOMELABORLEVELNM4,7,1)
5

Did that ,but same issue and it was their in Select statement but was not in Group by .So added .Still same issue

6

could you show your latest query, please

7 
SELECT EmployeeId 'EmpId',
LaborLevelName4 'Plant',
LaborLevelDsc3	'DeptName',
LaborLevelName6 'Employeetype',
PERSONFULLNAME 'Employee',
SUBSTRING(HOMELABORLEVELNM4,1,6) 'Dept' ,
SUBSTRING(HOMELABORLEVELNM4,7,1) 'SHIFT1',
ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours',
ROUND(SUM(WageAmount),2) 'Earnings'
FROM Totals
WHERE ApplyDate BETWEEN @from_timeframe AND @to_timeframe AND 
PayCodeName = 'Regular'
GROUP BY EmployeeId, Homelaborlevelnm4, LaborLevelDsc3, LaborLevelName6,LaborLevelName4,PERSONFULLNAME)
,
Hours_Earnings AS 
(SELECT EmployeeId 'EmpId',
LaborLevelName6 'Employeetype',
SUBSTRING(HOMELABORLEVELNM4,1,6) 'DEPT',
ROUND(SUM(TimeInSeconds/3600.00),2) 'Hours',
ROUND(SUM(WageAmount),2) 'Earnings
8

It got resolved .Thank you for @DaveMaxwell for helping to brainstorm the issue . It was resolved by changing the line as:
substring(laborlevelname4,1,6) “Dept”,
substring(laborlevelname4,7,1) “Shift”,

1 Like
9

this query you posted is seriously broken – the syntax errors should be obvious

but i’m glad you resolved your issue

10

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.