I am having difficulty using temp tables. I can successfully create the table but when I go to query it I get an error that it does not exist.
This will depend on what type of temp table and where you are using it in the app. Temp tables often only exist per session so it you use in another editor that uses a different connection. Same goes for using it in automation. Please provide more detail. Post full SQL/script and where you are using in the app.
I was able to get it to work using another editor (Aginity). However when I put it in Toad Data point I get an error
here is a test I ran:
Create TEMP Table AALDATA.test as select
1 as col1,
2 as col2,
3 as col3;
Then when I do a:
Select * from AALDATA.test
I get this error:
ODBC Database Error: ERROR: relation does not exist AALDATA.TEST
does Toad kill the connection after the code runs? is there a way to reset this?
Temp tables persist for the life of the session they are made in. Each SQL editor opens it's own connection. If you create the temp table in one SQL Editor and query from the temp table you are fine.
If you open a second editor the new connection cannot access the temp table because it exists in another session.