Ramco Ramco

Ramco Ramco

  • 442
  • 3.4k
  • 517.6k

Error - Inavlid Object Name CTE

Apr 27 2023 1:30 PM

Hi

  Getting error Inavlid Object Name CTE

ALTER PROC [dbo].[USP_SessionBookPlanningRecommendation] --'0','21,24,272,1415'
-- --'0','28,1328'
(
--@Rpl varchar(10),
@BookID VARCHAR(max),
@StudentID VARCHAR(max)
)
AS
BEGIN
declare @cols as nvarchar(max)='';
declare @query as nvarchar(max)='';

with CTE as
        (
        SELECT SP.[ID]
			  ,SP.[BookID]
			  ,B.[BookTitle]
			  ,B.[AmazonURL]
			  ,B.[NumberOfPages]
			  ,B.[Lexile]
			  ,B.BookRPL [CoreRPL]
			  ,(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) [SessionPlanStatus]
			  ,SP.[StudentID]
			  ,ST.[Name]
			  ,ST.[Name]+' ('+ST.[MobileNo]+')' [StudentName]
			  ,SP.[Status]
			  ,SP.[LastModifiedDate]
			  ,'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 [CoreRPL]
		  ,(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 [SessionPlanStatus]
		  ,T0.[StudentID]
		  ,T0.[Name]
		  ,T0.[StudentName]
		  ,T0.[Status]
		  ,T0.[LastModifiedDate]
		  ,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],[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],[ClosingStock], ' + @cols + ' from (select [BookID] ,BookTitle,[ComboBookName],[CoreRPL],[Lexile],[NumberOfPages],[SessionPlanStatus],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';
print @query
execute (@query) 
END

Thanks
 


Answers (5)