Unable to execute the temp tables all at a time

 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)

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
;