Unable to create view with temp table Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ?
Following is my query, I tried using WITH..AS , but it gives due to insert query.
create temporary table temp_testtbl as
select id,dte,name,hrs_total
FROM dttbl.dts limit 0;

insert into temp_testtbl 
select id,dte,name,hrs_total
FROM dttbl.dts 
group by id,dte,name,hrs_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.hrs_total) AS myhrs
   FROM temp_testtbl i
   JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;
A view can't have multiple statements within it, you have to express the entire thing as a SELECT. A subquery with the SELECT part of your INSERT can be used in place of the temp table reference. Below should give you a rough idea, although there are errors in the original SQL that I can't fix based on the information I have (eg, the first join condition references i.iex_id, which doesn't exist in the temp table or the subquery).
select i.dte, idm.empl_id, 1 as entity_id, sum(i.hrs_total) AS myhrs
FROM (
select id, dte, name, hrs_total
FROM dttbl.dts
group by id,dte,name,hrs_total
) AS i
JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;