Error with CTE when it doesnt recognize the table

Question

I'm trying to run this query, but unfortunately I keep getting the error of "first_table" doesn't exist. Is there a way to fix this. I've tried every scenario i could think of and still nothing. any help willl be very helpful

with first_table as
(
SELECT date_due,
customer_email,
SUM(budget) as monthly_budget
FROM Expense
WHERE canceling = 'TRUE'
GROUP by date_due,
customer_email
)

,get_invite_temp AS
(
SELECT pk.datetime
,em.provider
,em.email
,sum(distinct pk.profile_owner_id) as total_assistance
FROM invitee_clients pk

INNER JOIN profile_users em
on pk.profile_owner_id = em.id
WHERE pk.datetime::date >= dateadd(months, -12, date_trunc('month', getdate()))
group by 1,2,3
)

select first_table.monthly_budget,
get_invite_temp.total_assistance,
get_invite_temp.datetime,
get_invite_temp.provider

FROM first_table
INNER JOIN get_invite_temp ON first_table.customer_email = get_invite_temp.email

the goal is to have a table that looks like this:

datetime | Total a_assistance | monthly_budget | provider
2020-10-01 | 500 |. 2500 | attnt

Answer 1

On what platform are you running this? I see several things in the script which are not supported in SQL Server: date_trunc, months as argument to datetime, "GROUP BY 1, 2, 3".

Oh, I see now that you have use both tags for SQL Server and Postgres. Unfortunately, I cannot test on Postgres right now.

However, while working with your script, I noticed that you have a table priofile_users. Is that name correct? Or should that be profile_users without an extra i?

Also, I note that your final query refers to a column, first_table.total_assistance, but the CTE does not define this column.

Answer 2

I played with your query on Postgres, but I did not get any error about first_table not existing, but I got plentyful of other errors. Particularly with this line:

 dateadd(months, -12, date_trunc('month', getdate()))

I found that dateadd is not available in Postgres, it exists in SQL Server. On the other hand, date_trunc is only in Postgres, but is not in SQL Server. getdate(), yet again, is only in SQL Server; it is not in Postgres.

So where do you want your code to run?

Regardless of the platform, I this one looks odd to me:

sum(distinct pk.profile_owner_id) as total_assistance

Summing distinct values of an id?

If you want help to compose your query, you need to tell us what platform you are working on. We also need the CREATE TABLE statements with the actual data types and INSERT statements with sample data and the desired result given that sample.