Issue creating temp table

Trevor_Newman

Hi,

I am trying to run the following to create a temp table :-

drop table if exists table_a_url;
create local temporary table table_a_url 
ON COMMIT PRESERVE ROWS  
as /*+ direct */
select  a.*
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";

The statements seem to run but zero rows are inserted into temp table. 

The select stmt alone runs fine.

Any idea what gives?

We are using Vertica version - 07.01.0100

Thanks,

Lokesh_1

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)

Trevor_Newman

Hi Lokesh,

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

Thanks again.