Unable to execute the temp tables all at a time

San2

Hi , 

 

 

I'm trying to create some local temp tables and then from the local temp tables i want to select & pull the data from using all the local temp tables & drop them once data is fetched ... but when i tried to execute all at once its throwing error that the couldn't find the table or relationshiip errors. 

 

 

when i tried to create 1st local temp tables then execute select query then also same relationship errors.. 

 

can you please help me. how to run the query in one go so that i can calculate the time taken to execute the total

 

 

Thanks 

San2

 

 

 

Adrian_Oprea_1

 Not sure i follow you ? 

Can you post your SQL.

 

 See simple example:

dbadmin=> CREATE LOCAL TEMP TABLE temp_table1(x int) ON COMMIT PRESERVE ROWS;
CREATE TABLE
dbadmin=> insert into  temp_table1 values(1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from temp_table1;
 x
---
 1
(1 row)

dbadmin=> create LOCAL TEMP TABLE temp_table2(y int) ON COMMIT PRESERVE ROWS;
CREATE TABLE
dbadmin=> insert into temp_table2 select * from temp_table1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> drop table temp_table1;
DROP TABLE
dbadmin=> select * from temp_table2;
 y
---
 1
(1 row)

 Also you can use WITH

dbadmin=> with a as (select 1 from dual), 
b as (select * from a) 
select * from a union all select * from b;
?column?
----------
1
1
(2 rows)

San2

Hi , 

 

i tried to run this type of temp tables but unable to run all at once which throws an relationship error 

 

you can find the suffixtmp as tmp tables used 

 

CREATE LOCAL TEMP TABLE ctmp ON COMMIT PRESERVE ROWS AS 
SELECT
imp.user_id
FROM
A AS imp
b AS dsm
WHERE
imp.organization_id IN (SELECT DISTINCT ADV_ORGANIZATION_ID FROM dTmp WHERE ADV_ORGANIZATION_ID IS NOT NULL)
AND imp.impression_timestamp BETWEEN (SELECT OffsetStartDate FROM eTmp) AND (SELECT OffsetEndDate FROM eTmp)
AND dsm.placement_id = imp.placement_id AND dsm.creative_id = imp.creative_id
GROUP BY
imp.user_id
HAVING
COUNT(DISTINCT dsm.campaign_id) = 1
;