The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Unable to execute the temp tables all at a time

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

 

 

 

Comments

  •  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
    ;

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.