0

Hi I am working on one project, here what I am trying is, I want to insert records what I get using select statements into temp table. but it is giving error to me, can any one help me to solve this ?

ERROR: relation "temp_mytbl" does not exist SQL state: 42P01

following is my query

truncate table temp_mytbl;
create temporary table temp_mytbl as
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum  limit 0;

 insert into temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;
11
  • but it is giving error to me What statement generates the error? What is the error message generated by DB server precisely? Add DDLs for all 3 tables used into the question text.
    – Akina
    Commented Jul 3, 2020 at 8:52
  • syntax error near select Commented Jul 3, 2020 at 8:55
  • 1
    Please post the whole error message without any edition storing linebreaks, spaces counts and so on...
    – Akina
    Commented Jul 3, 2020 at 8:56
  • 1
    This error message means that the table/view temp_mytbl not exists in your database. So you must create it before use.
    – Akina
    Commented Jul 3, 2020 at 9:17
  • 1
    Remove TRUNCATE statement. And use one CREATE TABLE .. SELECT instead of 2 separate queries. PS. Your intermediate static table seems to be excess - use CTE.
    – Akina
    Commented Jul 3, 2020 at 9:21

2 Answers 2

1
WITH temp_mytbl AS ( -- without aggregation in output expression / HAVING clause
                     -- GROUP BY must be replaced with DISTINCT
                     -- which is less expensive
                     SELECT DISTINCT iex_id, dte, agent_name, schd_total
                     FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
                   )
SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM temp_mytbl i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no 
                               AND idm.src_type_id = 618 
                               AND soar_date(a.calendar_date_key) >= idm.start_dte 
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id /* , entity_id */ 
-- LIMIT without ORDER BY makes no sense
ORDER BY i.dte /* or another sorting expression */
LIMIT 100;

or the same in subquery form

SELECT i.dte, idm.empl_id, 1 AS entity_id, SUM(i.schd_total) AS s_schdhours
FROM ( SELECT DISTINCT iex_id, dte, agent_name, schd_total
       FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
     ) i
JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no 
                               AND idm.src_type_id = 618 
                               AND soar_date(a.calendar_date_key) >= idm.start_dte 
                               AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
GROUP BY i.dte, idm.empl_id 
ORDER BY i.dte
LIMIT 100;
2
  • hi akina, I appriciate your effort for this, it is helpful. this query is working fine. but using distinct it gives data for same id. what I was working to get the data in temp table, can you help me with group by ? Commented Jul 3, 2020 at 12:01
  • can you help me with this issue dba.stackexchange.com/questions/270764/… Commented Jul 10, 2020 at 21:28
-1
create table #temp_mytbl (
 iex_id int,
 dte int,
 agent_name varchar(10),
 schd_total float
 )

 insert into #temp_mytbl
select iex_id,dte,agent_name,schd_total
FROM source_xrx_iex6_sandy_1.iex6_agent_adh_sum 
group by iex_id,dte,agent_name,schd_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.schd_total) AS s_schdhours
   FROM #temp_mytbl i
     JOIN source_odw_usac.t_incontact_agentstatelog a ON a.winid = i.iex_id::text
     JOIN reporting_prod.idm_dte idm ON idm.src_id = a.agent_no AND idm.src_type_id = 618 AND soar_date(a.calendar_date_key) >= idm.start_dte AND soar_date(a.calendar_date_key) <= COALESCE(idm.end_dte, now()::date)
  GROUP BY i.dte, idm.empl_id limit 100;


  DROP TABLE #temp_mytbl
1
  • #temp_mytbl is an invalid identifier in Postgres (and standard SQL)
    – user1822
    Commented Jul 5, 2020 at 8:58

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.