Click here to Skip to main content
15,888,195 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
When i executing Stored Procedure it generating error which are mentioned below..

Msg 207, Level 16, State 1, Line 7
Invalid column name 'CL'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'H'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'A'.
Msg 207, Level 16, State 1, Line 15
Invalid column name 'P'.


What I have tried:

ALTER Procedure [dbo].[Pivot_Attendance2]


As  
Begin 

SELECT DISTINCT ReportingDate INTO #Dates
FROM EmployeesAttendance

ORDER BY ReportingDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, ReportingDate), 112)
				+ ']','[' + CONVERT(varchar,DATEPART(DAY, ReportingDate), 112) + ']')
FROM    #Dates
ORDER BY ReportingDate


-- Building the query with dynamic dates
--DECLARE @qry NVARCHAR(4000)
--SET @qry =
--'SELECT * FROM
--(SELECT EmpID, Status , ReportingDate
--FROM EmployeesAttendance)emp
--PIVOT (MAX(Status) FOR ReportingDate IN (' + @cols + ')) AS stat'

DECLARE  @qry NVARCHAR(4000) =
N'SELECT * 
FROM (SELECT     EmpID, ReportingDate, CASE WHEN [Days] IS null AND EXISTS
                          (SELECT     1
                            FROM          EmpApplication
                            WHERE      EmployeesAttendance.Empid = EmpApplication.Empid AND 
                            (ReportingDate >= LeavFrom AND ReportingDate <= LeavTo))
                             THEN CL
                             WHEN [Days] IS null AND
                       EXISTS
                          (SELECT     1
                            FROM          Holidays
                            WHERE      ReportingDate = HolidayDate) 
                            THEN H 
                                                        
                            WHEN [Days] IS null THEN A WHEN [Days] = 1 THEN P END AS Status,

DATEPART(DAY, EmployeesAttendance.ReportingDate)as DDate

FROM EmployeesAttendance  )emp
PIVOT (MAX(Status) FOR DDate IN (' + @cols + ')) AS stat 
'
-- Executing the query

EXEC(@qry)
 
END
Posted
Updated 25-Jan-19 21:17pm

1 solution

Based on the error message you don't have columns CL, A, or H in table EmployeesAttendance.

If you're trying to use literals then surround the values with apostrophes. In other words
SQL
... AND ReportingDate <= LeavTo))
THEN ''CL''
WHEN ...

Otherwise, re-check the schema definition of the table.
 
Share this answer
 
v2
Comments
akhter86 26-Jan-19 3:29am    
Dear @Wendelius

if i am giving apostrophes then executing Store Procedure it generating error (Msg 102, Level 15, State 1, Procedure Pivot_Attendance2, Line 13
Incorrect syntax near 'CL'.
Msg 137, Level 15, State 2, Procedure Pivot_Attendance2, Line 28
Must declare the scalar variable "@qry".)
Wendelius 26-Jan-19 3:36am    
Since the apostrophes are inside a string you you need to use double apostrophes. Sorry about that!

See the updated example.
akhter86 26-Jan-19 3:57am    
thanks Wendelius....
For you support and thanks again ...
Wendelius 26-Jan-19 4:57am    
You're welcome :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900