0

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;
1
  • Unrelated to your problem, but: Postgres 9.3 is no longer supported you should plan an upgrade as soon as possible.
    – user1822
    Jul 7, 2020 at 18:04

1 Answer 1

2

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;
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.