Issue creating temp table

Hi Trevor,

I recreated same scenario as mentioned above and I could see records in temp table from same session.

Did you check those rows on same session or other session as temp tables can show records in same session only where it was created.


create local temporary table table_a_url
dbadmin-> ON COMMIT PRESERVE ROWS
dbadmin-> as /*+ direct */
dbadmin-> select  a.*
dbadmin-> from source_url a
dbadmin->  join (
dbadmin(> select
dbadmin(> id,"source","url",count(*)
dbadmin(> from source_url
dbadmin(> group by 1,2,3
dbadmin(> having count(*) > 1) b on b.id=a.id and b."source"=a."source" and b."url"=a."url";
CREATE TABLE



select  a.*dbadmin-> from source_url a
join (
select
id,"source","url",count(*)
from source_url
group by 1,2,3
having count(*) > 1) b on b.id=a.id and b."source"=a."source" and b."url"=a."url";

 id | source |     url     |  description  |     other
----+--------+-------------+---------------+---------------
  1 | prod   | vertica.com | prod server 1 | low priority
  1 | prod   | vertica.com | prod server 2 | high priority
(2 rows)


select * from table_a_url ;
 id | source |     url     |  description  |     other
----+--------+-------------+---------------+---------------
  1 | prod   | vertica.com | prod server 1 | low priority
  1 | prod   | vertica.com | prod server 2 | high priority
(2 rows)

Hi Lokesh,

Thanks for your answer - I have found out my issue and all is fixed now.

Thanks again.