Error - Inavlid Object Name CTE

Hi AMit

  I have written below code but still it is giving same error.

;with CTE as
        (
        SELECT SP.[ID]
			  ,SP.[BookID]
			  ,B.[BookTitle]
			  ,B.[AmazonURL]
			  ,B.[NumberOfPages]
			  ,B.[Lexile]
			  ,B.BookRPL [Core RPL]
			  ,(select [CombosBookName] from [dbo].[ComboBookMaster] where [CombosBookID] = B.CombosBookID) [ComboBookName]
			  ,CASE WHEN B.[BookAvailableForSession] = 0 THEN 'N' ELSE '' END AS [Negative List]
			  ,(Select TOP 1 V.[CurrentStatus] FROM [View_SessionDetails] V WHERE V.ID=B.ID) [Session Plan Status]
			  ,SP.[StudentID]
			  ,ST.[Name]
			  ,ST.[Name]+' ('+ST.[MobileNo]+')' [StudentName]
			  ,SP.[Status]
			  ,'H' AS [EntryType]
			  ,(select closingstock from [View_WareHouseStockSummary] where BookId = SP.[BookID]) as [ClosingStock]
		  FROM [SessionBookPlanningHistoricalData] SP
		  JOIN [BookDetails] B
		  ON SP.BookID=B.[ID]
		  JOIN [StudentDetails] ST
		  ON SP.[StudentID]=ST.[LoginCode]
		  WHERE SP.[Deleted]=0

        Union All
        SELECT T0.[ID]
		  ,T0.[BookID]
		  ,T0.[BookTitle]
		  ,(Select T1.[AmazonURL] from [BookDetails] T1 where T1.ID = T0.BookID)  [AmazonURL]
		  ,(Select T1.[NumberOfPages] from [BookDetails] T1 where T1.ID = T0.BookID) [NumberOfPages]
		  ,(Select T1.[Lexile] from [BookDetails] T1 where T1.ID = T0.BookID) [Lexile]
		  ,T0.[CoreRPL] as [Core RPL]
		  ,(select T1.[CombosBookName] from [dbo].[ComboBookMaster] T1 where T1.[CombosBookID] = (select T2.CombosBookID from [BookDetails] T2 where T2.Id = T0.BookID)) [ComboBookName]
		  ,T0.[NegativeList] AS [Negative List]
		  ,T0.[SessionPlanStatus] AS [Session Plan Status]
		  ,T0.[StudentID]
		  ,T0.[Name]
		  ,T0.[StudentName]
		  ,T0.[Status]
		  ,T0.[EntryType]
		  ,T0.[ClosingStock]
		FROM [SessionBookPlanningDetailed] T0
		WHERE T0.[HistoricalDataCount]=0
        )
select * from  CTE

select @cols = @cols + QUOTENAME(StudentName) + ',' from (Select distinct StudentName from CTE where StudentID in (select [value] from string_split(@StudentID,','))) as tmp
select @cols = substring(@cols,0,len(@cols))
set @query = 'select [BookId] [Book ID],BookTitle [Book],[Core RPL],[Negative List],[Session Plan Status],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[Core RPL],[Negative List],[Session Plan Status],Status,[ClosingStock],StudentName from CTE where (('''+@BookID+'''=''0'' OR [BookId] IN('+ cast(@BookID as varchar(Max))+')))) x pivot
( Max(Status) for StudentName in (' + @cols + ')) piv order by BookTitle';
----set @query = 'select [BookId] [Book ID],BookTitle [Book],[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],Status,[ClosingStock],StudentName from View_SessionBookPlanning where (('''+@BookID+'''=''0'' OR [BookId] IN('+ cast(@BookID as varchar(Max))+')))) x pivot
----( Max(Status) for StudentName in (' + @cols + ')) piv order by BookTitle';
print @query
execute (@query) 

Thanks

A CTE exists only within the query where it is defined and it can't be referenced from outside that query.

Check if the CTE is being used within the same query where it is defined.

A common way to check if a Common Table Expression (CTE) is being used within the same query where it is defined is to add a SELECT statement after the CTE definition that selects all columns from the CTE. Here's an example:

 
WITH MyCTE AS (
  SELECT * FROM MyTable
)
SELECT * FROM MyCTE;
 

If the query runs without any errors, it means that the CTE is defined correctly and can be used within the same query. If you are getting an "Invalid object name 'CTE'" error, it could be because you are trying to reference the CTE in a subsequent statement outside the current scope. In this case, you may need to rewrite the query to ensure that the CTE is being used within the same scope where it is defined.

The error "Invalid Object Name CTE" indicates that SQL Server cannot recognize the Common Table Expression (CTE) defined in the query.

Please check if the CTE name "CTE" is defined properly and is not conflicting with any other object name. Also, ensure that the query is executed in the correct database context.

If the issue persists, please provide the complete error message and the SQL Server version you are using for further assistance.