question

ASHMITP-0361 avatar image
0 Votes"
ASHMITP-0361 asked MelissaMa-msft edited

Getting error while creating temp table

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ASHMITP-0361,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Thank you for understanding!

Best regards,
Melissa

1 Vote 1 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @ASHMITP-0361,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.