Getting error while creating temp table

Question

Hi there,
Please assist with the error -
I have a script like below and getting an error 'Invalid object name 'PayCalendar' '
script -

WITH PayCalendar as (
SELECT
a,
b,
c,
ROW_NUMBER() OVER (PARTITION BY a,b order by c desc) as rn
FROM table
WHERE a=1
)
SELECT
a,
b,
c
from PayCalendar WHERE rn=1

SELECT DISTINCT
ReferenceDate,
(SELECT a FROM PayCalendar pc WHERE pc.rn=1 and ReferenceDate BETWEEN pc.a and pc.b) as PAY_PERIOD_START_DATE,
(SELECT pc.b FROM PayCalendar pc WHERE pc.rn=1 and ReferenceDate BETWEEN pc.a and pc.b) as PAY_PERIOD_END_DATE  
FROM table

Many Thanks

Answer 1

Invalid object name 'PayCalendar

You have a CTE = Common Table Expression, not a temp table.
And a CTE can be used only once in the next following SQL statement. You use it a second time, which is not possible and causes the error.

Answer 2

Hi @ASHMITP ,

Welcome to Microsoft Q&A!

Based on limited information you provided, you could have a try to remove the second part and update the query like below:

WITH PayCalendar as (  
SELECT  
a,  
b,  
c,  
ROW_NUMBER() OVER (PARTITION BY a,b order by c desc) as rn  
FROM [table]  
)  
SELECT DISTINCT  
ReferenceDate,  
pc.a,  
pc.b  
FROM [table] t   
left join PayCalendar pc  
on t.a=pc.a and t.b=pc.b  
where pc.rn=1 and ReferenceDate BETWEEN pc.a and pc.b  

If above is not working, please provide CREATE TABLE statements for your table together with INSERT statements with sample data ,and provide the expected result of the sample.

Besides, A CTE is basically a disposable view. It only persists for a single statement, and then automatically disappears.

So you would get the 'Invalid object name' error when you execute below query:

 ;with cte as (select a from [table])  
 select * from cte  
 select * from cte  

Below queries are correct.

 ;with cte as (select a from [table])  
 select * from cte  

OR

 ;with cte as (select a from [table])  
 ,cte1 as (select * from cte where a=1)  
 select * from cte1  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.